MySQL COUNT() function with group by
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:
SELECT country,COUNT(*)
FROM author
GROUP BY 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:
SELECT country,pub_city,COUNT(*)
FROM publisher
GROUP BY country,pub_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)
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join