How to Count Records After Grouping Data with SQL?

Statistics the number of records after SQL grouping

When using the GROUP BY statement with the SELECT clause, you can use an aggregate function to count the number of records in each grouping. This is useful for getting summary statistics, such as the total number of users per town.

Use GROUP BY for counting

To count records after grouping, use the GROUP BYaggregation function in the COUNT(*) clause:

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

This query will return a list of towns and the total number of users in each town.

Other aggregate functions

In addition to COUNT, you can also use other aggregate functions with GROUP BY, for example:

• MAX: Returns the maximum value of the field in each group.
• MIN: Returns the minimum value of the field in each group.
• COUNT DISTINCT: Returns the count of unique values ​​for the field in each group.

Example

Consider a table named user which contains a column named town. The following query counts the total number of users in each town:

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

This query will produce the following results:

城镇	用户总数
哥本哈根	58
纽约	58
雅典	58

Use variables to store the total number

Alternatively, you can declare a variable to store the total number of users and then choose the value of that variable:

<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 provides flexibility when using totals in subsequent calculations or operations.

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