How to Count Records After a SQL GROUP BY Clause?

Count the number of records after GROUP BY

When using a SQL statement that contains both SELECT and GROUP BY clauses, you need to determine the total number of records after grouping. This article aims to provide guidance on how to achieve this goal directly in SQL queries.

For example, we have a table named user, which contains a town column. The goal is to get a list of different towns and the total number of users for each town. To do this, we can use the COUNT aggregate function with the GROUP BY clause:

<code class="language-sql">SELECT `town`, COUNT(*) AS `total_users`
FROM `user`
GROUP BY `town`;</code>

By using the COUNT(*) function, we count the number of occurrences of each town after the grouping is completed. The generated table will provide the required output, which includes the town names and their respective user counts.

It should be noted that aggregate functions such as COUNT, MAX, and COUNT DISTINCT can be used in the GROUP BY statement to further manipulate data.

Additionally, a variable can be declared to store the total number of users and then added to subsequent SELECT statements to display the value:

<code class="language-sql">DECLARE @numOfUsers INT;
SET @numOfUsers = (SELECT COUNT(*) FROM `user`);

SELECT DISTINCT `town`, @numOfUsers AS `total_users` FROM `user`;</code>

This approach allows us to explicitly specify the total number of users as well as the town name in the final output.

The above is the detailed content of How to Count Records After a SQL GROUP BY Clause?. For more information, please follow other related articles on the PHP Chinese website!