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