How to Add a SUM Row to a Grouped COUNT SQL Query Result?

Adding a SUM Row in a Grouped COUNT SQL Query

In a SQL query, it is often necessary to group rows based on a certain criterion and count the number of occurrences in each group. To this end, one can utilize the COUNT function. However, there may be instances where you also wish to display the total count of all the groups combined.

Consider the following table containing two fields, ID and Name:

ID	Name
1	Alpha
2	Beta
3	Beta
4	Beta
5	Charlie
6	Charlie

We want to group these rows by their Name and count the number of occurrences for each group. Additionally, we want to add a "SUM" row at the bottom of the table, indicating the total count of all the groups.

To achieve this, we can employ the following query:

SELECT name, COUNT(name) AS count, SUM(COUNT(name)) OVER() AS total_count
FROM Table
GROUP BY name

Let's break down the query:

• The SELECT statement retrieves the name and the count for each group.
• The COUNT(name) function counts the number of occurrences for each group name.
• The SUM(COUNT(name)) OVER() expression calculates the total count of all the groups. The OVER() function ensures that this calculation is performed across all rows in the table.
• The GROUP BY statement groups the rows by the name field.

The result of this query will be as follows:

Name	Count	Total_Count
Alpha	1	6
Beta	3	6
Charlie	2	6
SUM	6	NULL

As you can observe, the "SUM" row has been added to the bottom of the table, displaying the total count of all the groups, which in this case is 6.

The above is the detailed content of How to Add a SUM Row to a Grouped COUNT SQL Query Result?. For more information, please follow other related articles on the PHP Chinese website!