Can I use the minimum value of a column created with AS?-CodePudding

In my task I needed to create a new column that shows how many singers are in some specific bands. So far I did this in the SELECT section:

SELECT bands.name, count(\*) AS singernumber
FROM....

So my quiestion is how can I reduce the shown datas to only the ones with the biggest value. Can I use MIN refered to the "singernumber" column?

SELECT bands.name, count(\*) AS singernumber
FROM bands, artists
WHERE bands.id=artists.id
AND artists.role LIKE "*singer*"
GROUP BY bands.name, bands.id;

CodePudding user response：

You canuse HAVING to exluse like in the follwoing alm that more than two singers.

but you should switch to JOIN, comma separated are not used anymore

SELECT bands.name, count(*) AS singernumber 
FROM bands INNER JOIN artists 
ON bands.id=artists.id 
WHERE artists.role LIKE "*singer*" 
GROUP BY bands.name, bands.id
HAVING COUNT)(*) > 2;