Select statement with count

Question

I want to count the number of ABC group using id.

SELECT group, count(id) as total FROM `user` WHERE group=`ABC`;

What's wrong? Many thanks.

Answer 1

If you want to get COUNT of users, who has the "group" field = "ABC"

SELECT count(id) as total FROM user WHERE group='ABC';

Also, it's better to avoid using SQL keywords in column names (GROUP is an SQL keyword)

Answer 2

Try this:

SELECT group, count(id) as total FROM `user`
     group by group having group like 'ABC';

Answer 3

Include the columns in the select list in group by clause when using aggregate functions.

SELECT group, count(id) as total FROM user 
WHERE group=`ABC`
GROUP BY group

Else simply get the count with out using other columns in the select statement.

SELECT count(id) as total FROM user 
WHERE group=`ABC`