How to Count Records After Grouping in SQL?

SQL record counting method after grouping

Counting records after a grouping operation is also useful when using SQL GROUP BY queries with the SELECT clause. This way you can easily get aggregated statistics for each grouping.

Use GROUP BY for aggregate counting

To count grouped records, simply include the SELECT aggregate function in the COUNT() statement, along with the column you want to group on. For example, if you have a table called "users" and you want to select different towns and count the total number of users in each town:

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

This query will return a result set with one column for each town and one column for the total number of users in that town.

Contains total count

If you also want to display a column containing the total number of users for all rows, you can use a subquery to calculate this value and join it to the grouped result set. Alternatively, you could use a database-specific variable to store the total count and then reference it in the main query.

For example, in MySQL you can use the following query to include the total count column:

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

You can easily get group level and total count in SQL query by using aggregate functions and subqueries.

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