SQL DISTINCT plus counter

Question

Currently, I have a table with the album name, artist and title in it (for Mp3). On my webpage on the left, all album names are currently displayed using:

SELEC DISTINCT `album` FROM `mp3` 

But I would like to display the number of each album next to it, for example:

 A Very Good Album (3) 

Indicating that there is 3 in this entry. Can I do this using SQL? Select "Distinct", but also find out how many of them are associated with this album?

Answer 1

Try the following:

 SELECT `album`, COUNT(*) FROM `mp3` GROUP BY `album` 

Answer 2

Instead of DISTINCT this is really the base COUNT() aggregate , grouped by album .

 SELECT album, COUNT(*) AS num_records FROM mp3 GROUP BY album 

If you want in brackets inside an SQL query, use CONCAT() . This is probably best left for your application to display.

 SELECT /* The album title, and count in () as one column as a single string */ /* like "A Very Good Album (3)" */ CONCAT(album, ' (', COUNT(*), ')') AS album FROM mp3 GROUP BY album 

Answer 3

Do you want group by :

 select album, count(*) from mp3 group by album 

Answer 4

use a group and create a line like this

 SELECT CONCAT(`album`, ' (', COUNT(*), ')') as album FROM `mp3` GROUP BY `album` 

Answer 5

Take a look at Count() and Group By

Answer 6

 SELECT DISTINCT CONCAT(album, ' (', COUNT(DISTINCT album), ')') AS album FROM mp3 GROUP BY album