Display all values ​​in a GROUP BY group

Question

How to get all the values ​​in the group according to the instructions?

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 is a funny trick that you can use when COUNT (column) counts the number of non-zero values; you also use self-connection (in doing so):

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 will work in all versions of SQL; not all options will allow you to summarize on a Boolean expression, which will undoubtedly be faster and more direct with its support.

Another way to write this:

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 deletes all entries that have no status A, so the name is xyzmissing.

, A:

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

---

, MySQL:

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