CODEWITHSUNDEEP
sqlmysql
shantanuo
shantanuo

Reputation: 32316

Display all values in a GROUP BY

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 | 
+------+----------+

Upvotes: 4

Views: 21733

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 753695

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;

Upvotes: 4

Peter Lang
Peter Lang

Reputation: 55524

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 )
...

Upvotes: 4

Related Questions