COUNT with condition and group : Count : Select Clause SQL / MySQL


SQL / MySQL » Select Clause » Count »

 

COUNT with condition and group



/*
mysql> SELECT species, sex, COUNT(*) FROM Bird
    ->     WHERE sex IS NOT NULL
    ->     GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| Bus     | m    |        2 |
| Car     | f    |        1 |
+---------+------+----------+
2 rows in set (0.00 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);

/* Or, if you wanted the number of animals per sex only for animals whose 

sex is known:
*/

SELECT species, sex, COUNT(*FROM Bird
    WHERE sex IS NOT NULL
    GROUP BY species, sex;


           
       



Leave a Comment / Note


 
Verification is used to prevent unwanted posts (spam). .

Follow Navioo On Twitter

SQL / MySQL

 Navioo Select Clause
» Count