SQL SUM() function with group by
SUM() function with group by
Overview
The aggregate functions summarize the table data. Once the rows are divided into groups, the aggregate functions are applied in order to return just one value per group. It is better to identify each summary row by including the GROUP BY clause in the query results. All columns other than those listed in the GROUP BY clause must have an aggregate function applied to them.
The SUM() function in SQL is a powerful aggregate function that calculates the total sum of a numeric column. When combined with the GROUP BY clause, it allows you to summarize data based on unique values in specified columns. This is particularly useful for generating reports and insights from large datasets. To effectively use SUM(), all non-aggregated columns must be included in the GROUP BY clause. This ensures that you get one result row per unique group.
Query Example
Sample table: orders
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
...........
To retrieve the total ADVANCE_AMOUNT for each AGENT_CODE from the orders table, ensuring that each agent appears only once, you can use the following SQL query:
SQL Code:
-- Selecting columns: agent_code, SUM(advance_amount)
-- From the 'orders' table
SELECT agent_code, SUM(advance_amount)
-- Grouping the results by the 'agent_code' column
-- Result: Total sum of 'advance_amount' for each 'agent_code'
FROM orders
GROUP BY agent_code;
Explanation:
- SELECT COUNT(*): This is the main part of the SQL query. It uses the COUNT(*) function to count the number of rows in the 'orders' table. The * is a wildcard character that counts all rows regardless of their content. The result will be a single row with a single column containing the total number of rows in the 'orders' table.
- FROM orders: This specifies the source of the data for the query, which is the 'orders' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'orders' table.
- GROUP BY agent_code: This clause groups the results based on agent_code, ensuring the aggregate function applies to each unique agent.
Output:
AGENT_CODE SUM(ADVANCE_AMOUNT) ---------- ------------------- A004 2100 A002 3500 A007 500 A009 100 A011 900 A012 450 A010 3700 A013 3200 A001 200 A008 3300 A006 600 A005 3100 A003 1000
Visual Presentation:
Advanced Usage: SUM() with Multiple Columns
Sample table: customer
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | ........
To calculate the combined total of OPENING_AMT and RECEIVE_AMT from a customer table, use the following query:
SQL Code:
-- Selecting columns: cust_city, SUM(opening_amt + receive_amt)
-- From the 'customer' table
SELECT cust_city, SUM(opening_amt + receive_amt)
-- Grouping the results by the 'cust_city' column
-- Result: Total sum of the sum of 'opening_amt' and 'receive_amt' for each 'cust_city'
FROM customer
GROUP BY cust_city;
Explanation:
- SELECT cust_city, SUM(opening_amt + receive_amt): This is the main part of the SQL query. It selects two columns: 'cust_city' and the sum of 'opening_amt' and 'receive_amt' for each city. The SUM() function is used to calculate the total sum of these amounts, which are summed up for each distinct 'cust_city'.
- FROM customer: This specifies the source of the data for the query, which is the 'customer' table. It indicates that the 'customer' table will be queried to retrieve the necessary data for processing.
- GROUP BY cust_city: This clause groups the result set by the 'cust_city' column. The GROUP BY clause is used to aggregate the rows based on the values in the 'cust_city' column. This means that the calculation performed in the SELECT statement will be applied separately for each unique value in the 'cust_city' column.
Output:
CUST_CITY SUM(OPENING_AMT+RECEIVE_AMT) ----------------------------------- ---------------------------- Bangalore 62000 Brisban 41000 Chennai 51000 Hampshair 10000 London 48000 Mumbai 51000 New York 32000 San Jose 12000 Torento 46000
Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition
Frequently Asked Questions (FAQ) - SQL SUM() Function with GROUP BY
1. What is the purpose of the SQL SUM() function when used with GROUP BY?
The SQL SUM() function calculates the total of a numeric column for grouped data. When used with GROUP BY, it allows for summarizing values based on unique entries in specified columns.
2. Why is the SQL GROUP BY clause important?
The GROUP BY clause is essential because it defines how the result set is divided into groups. Each unique value in the specified column(s) will have one summary row in the results, ensuring accurate aggregations.
3. What are the requirements for using SQL SUM() with GROUP BY?
When using the SUM() function with GROUP BY, all columns in the SELECT statement that are not included in the GROUP BY clause must be aggregated. This ensures that the query adheres to SQL standards and produces valid results.
4. Can we group by multiple columns?
Yes, you can group by multiple columns in SQL. This allows for more complex aggregations and summarizations based on combinations of values from different columns.
5. How can we ensure our results are clear and informative?
Including descriptive labels and ensuring that the GROUP BY columns are relevant will help clarify the results. Additionally, using aliases for calculated fields can enhance readability.
6. What are common use cases for SQL SUM() with GROUP BY?
Common use cases include generating financial reports, analyzing sales data, summarizing transactions by categories, and reporting on inventory levels across different locations.
7. Are there performance considerations when using SQL SUM() with GROUP BY?
Yes, performance can be affected by the size of the dataset and the complexity of the GROUP BY operation. Indexing relevant columns may improve query performance.
Here is a slide presentation of all aggregate functions.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: SUM function
Next: SUM and COUNT Using Variable and inner join