Grouping group by

where VS having

  • WHERE filters rows, and HAVING can't use aggregate function to filter rows after
  • WHERE, and HAVING can use aggregation function to filter packets
  • WHERE to filter before data grouping, HAVING After data grouping, filter

group summary WITH ROLLUP

select class,ssex,count(sname) from student group by class,ssex;
+-------+------+--------------+
| class | ssex | count(sname) |
+-------+------+--------------+
| 95031 | Female | 1 |
| 95031 | Male | 2 |
| 95033 | Female | 1 |
| 95033 | Male | 2 |
+-------+------+--------------+
select class,ssex,count(sname) from student group by class, Ssex with rollup;
+-------+------+--------------+
| class | ssex | count(sname) |
+-------+------+--------------+
| 95031 | Female | 1 |
| 95031 | Male | 2 |
| 95031 | NULL | 3 |
| 95033 | Female | 1 |
| 95033 | Male | 2 |
| 95033 | NULL | 3 |
| NULL | NULL | 6 |
+-------+------+--------------+