Count and group by two columns : Count « Select Clause « SQL / MySQL

Count and group by two columns

/*

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;

Related examples in the same category