How to Count Records within Groups and Get the Total Count in SQL?

Record count after GROUP BY in SQL

When using SQL GROUP BY queries that include SELECT, it is often also necessary to calculate the total number of records. Here's how to do this directly in a SQL statement:

To calculate the total number of records within each group, simply use the COUNT aggregate function with GROUP BY. For example, let's say you have a users table and want to list unique towns and the number of users in each town:

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

This query will return a table with two columns: town, representing unique town names; COUNT(town), showing the total number of users in each town.

Additionally, you can use other aggregate functions with GROUP BY, such as MAX, MIN, COUNT DISTINCT, etc.

Update:

You may also want to retrieve the total number of users across all towns. To do this, you can define a variable using the COUNT aggregate function:

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

You can then select different towns and the previously defined variables:

<code class="language-sql">SELECT DISTINCT `town`, @numOfUsers FROM `user`;</code>

This will provide a list of towns along with the total number of users.

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