Mega Code Archive
Count and group by two columns
<![CDATA[
/*
mysql> /* Number of animals per combination of species and sex: */
mysql> SELECT species, sex, COUNT(*) FROM Bird GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| Bus | m | 2 |
| Car | f | 1 |
+---------+------+----------+
2 rows in set (0.02 sec)
*/
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
INSERT INTO Bird VALUES ('BlueBird','Joe','Car','f','1999-03-30',NULL);
INSERT INTO Bird VALUES ('RedBird','Yin','Bus','m','1979-04-30',1998-01-30);
INSERT INTO Bird VALUES ('RedBird','Yin','Bus','m','1998-01-30',NULL);
/* Number of animals per combination of species and sex: */
SELECT species, sex, COUNT(*) FROM Bird GROUP BY species, sex;
]]>