Display all values in a GROUP BY

Question

How do I get all the values in a group by statement?

mysql>select * from mytable;
+------+--------+--------+
| name | amount | status |
+------+--------+--------+
| abc  |     12 | A      | 
| abc  |     55 | A      | 
| xyz  |     12 | B      | 
| xyz  |     12 | C      | 
+------+--------+--------+
4 rows in set (0.00 sec)

mysql>select name, count(*) from mytable where status = 'A' group by name;
+------+----------+
| name | count(*) |
+------+----------+
| abc  |        2 | 
+------+----------+
1 row in set (0.01 sec)

Expected result:

+------+----------+
| name | count(*) |
+------+----------+
| abc  |        2 | 
| xyz  |        0 | 
+------+----------+

Answer 1

There's a funny trick you can use where COUNT(column) counts the number of non-null values; you also use a self-join (when doing this):

SELECT a.name, COUNT(b.name)
  FROM mytable AS a LEFT OUTER JOIN mytable AS b
    ON a.name = b.name AND b.status = 'A'
 GROUP BY a.name;

This would work in all versions of SQL; not all variants will allow you to sum on a Boolean expression, which is undoubtedly faster and more direct when supported.

Another way to write it is:

SELECT a.name, COUNT(b.name)
  FROM mytable AS a LEFT OUTER JOIN
       (SELECT name FROM mytable WHERE status = 'A') AS b
    ON a.name = b.name
 GROUP BY a.name;

Answer 2

Your current solution removes all records which do not have status A, so name xyz is missing.

This should give you the distinct names and the count of records which have status A:

Select name, Sum( status = 'A' )
From mytable
Group By name;

---

This general solution would also work with other DBs than MySQL:

Select name, Sum( Case When status = 'A' Then 1 Else 0 End )
...