MySQL COUNT() function with group by

Last update on April 27 2024 08:09:22 (UTC/GMT +8 hours)

COUNT() function with group by

In this page we have discussed how to use MySQL COUNT() function with GROUP BY.

Example:

The following MySQL statement will show number of author for each country. The GROUP BY clause groups all records for each country and then COUNT() function in conjunction with GROUP BY counts the number of authors for each country.

Sample table: author

Code:


-- This SQL query counts the number of authors in each country.
SELECT country, COUNT(*) -- Selects the country and counts the number of authors in each country
FROM author -- Specifies the table from which to retrieve data (author table)
GROUP BY country; -- Groups the results by country, so that the count is calculated for each country

Explanation:

This SQL query retrieves data from the author table.

It counts the number of authors in each country.

The GROUP BY clause ensures that the results are grouped by country, allowing for separate counts to be calculated for each country.

Here's how the process works:

The query selects the country (country) and counts the number of authors in each country using COUNT(*).

It groups the results by country using GROUP BY country, ensuring that the count is calculated for each country.

Finally, the query returns the country and the count of authors for each country.

Output:

mysql> SELECT country, COUNT(*)
    -> FROM author
    -> GROUP BY country;
+-----------+----------+
| country   | COUNT(*) |
+-----------+----------+
| Australia |        2 | 
| Brazil    |        1 | 
| Canada    |        2 | 
| Germany   |        1 | 
| India     |        1 | 
| UK        |        4 | 
| USA       |        4 | 
+-----------+----------+
7 rows in set (0.00 sec)

Pictorial Presentation:

MySQL COUNT() function with group by on multiple columns

The following MySQL statement returns number of publishers in each city for a country. Grouping operation is performed on country and pub_city column with the use of GROUP BY and then COUNT() counts the number of publishers for each groups.

Sample table: publisher

Code:


-- This SQL query counts the number of publishers in each city for each country.
SELECT country, pub_city, COUNT(*) -- Selects the country, publisher city, and counts the number of publishers in each city for each country
FROM publisher -- Specifies the table from which to retrieve data (publisher table)
GROUP BY country, pub_city; -- Groups the results by country and publisher city, so that the count is calculated for each combination of country and city

Explanation:

This SQL query retrieves data from the publisher table.

It counts the number of publishers in each city for each country.

The GROUP BY clause ensures that the results are grouped by both country and publisher city, allowing for separate counts to be calculated for each combination of country and city.

Here's how the process works:

The query selects the country (country), publisher city (pub_city), and counts the number of publishers in each city for each country using COUNT(*).

It groups the results by both country and publisher city using GROUP BY country, pub_city, ensuring that the count is calculated for each combination of country and city.

Finally, the query returns the country, publisher city, and the count of publishers for each combination of country and city.

Output:

mysql> SELECT country,pub_city,COUNT(*)
    -> FROM publisher
    -> GROUP BY country,pub_city;
+-----------+-----------+----------+
| country   | pub_city  | COUNT(*) |
+-----------+-----------+----------+
| Australia | Adelaide  |        1 | 
| India     | Mumbai    |        1 | 
| India     | New Delhi |        1 | 
| UK        | Cambridge |        1 | 
| UK        | London    |        1 | 
| USA       | Houstan   |        1 | 
| USA       | New York  |        2 | 
+-----------+-----------+----------+
7 rows in set (0.00 sec)

Previous: COUNT()
Next: COUNT(DISTINCT)