SQL SUM() function with group by
SUM() function with group by
SUM is used with a GROUP BY clause.
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 resulst. All columns other than those listed in the GROUP BY clause must have an aggregate function applied to them.
Example:
To get data of 'agent_code' and the sum of 'advance_amount' for each individual 'agent_code' from the 'orders' table with the following condition -
1. same 'agent_code' should not come twice,
the following SQL statement can be used :
Sample table: orders
SQL Code:
SELECT agent_code,
SUM (advance_amount)
FROM orders
GROUP BY agent_code;
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
Pictorial Presentation:

SQL SUM() using multiple columns with group by
To get data of 'cust_city' and the sum of 'opening_amt' and 'receive_amt' for each individual 'cust_city' from the 'customer' table with the following condition -
1. same 'cust_city' should not come more than once,
the following SQL statement can be used:
Sample table: customer
SQL Code:
SELECT cust_city,
SUM (opening_amt + receive_amt)
FROM customer
GROUP BY cust_city;
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
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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join