content_image/859109.jpg content_image/859110.jpg content_image/859116.jpg
You need not retrieve an entire table when you use COUNT() . For example, the previous query, when performed just on dogs and cats, looks like this: 

mysql> SELECT species, sex, COUNT(*) FROM pet WHERE species = ’dog’ OR species = ’cat’ GROUP BY species, sex; 

<img src='content_image/859109.jpg'>

Or, if you wanted the number of animals per sex only for animals whose sex is known: 

mysql> SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex; 

<img src='content_image/859110.jpg'>

If you name columns to select in addition to the COUNT()  value, a GROUP BY  clause should be present that names those same columns. Otherwise, the following occurs: 

•If the ONLY_FULL_GROUP_BY  SQL mode is enabled, an error occurs: 

mysql> SET sql_mode = ’ONLY_FULL_GROUP_BY’; Query OK, 0 rows affected (0.00 sec) 

mysql> SELECT owner, COUNT(*) FROM pet; ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ’menagerie.pet.owner’; this is incompatible with sql_mode=only_full_group_by 

•If ONLY_FULL_GROUP_BY  is not enabled, the query is processed by treating all rows as a single group, but the value selected for each named column is nondeterministic. The server is free to select the value from any row: 

mysql> SET sql_mode = ’’; Query OK, 0 rows affected (0.00 sec) 

mysql> SELECT owner, COUNT(*) FROM pet; 

<img src='content_image/859116.jpg'>

1 row in set (0.00 sec) 

See also Section 12.20.3, “MySQL Handling of GROUP BY” . See Section 12.20.1, “Aggregate Function Descriptions”  for information about COUNT( expr )  behavior and related optimizations. 

## 3.3.4.9 Using More Than one Table 

