SQL SUM() and COUNT() using variable

SUM() and COUNT() functions

SUM of values of a field or column of a SQL table, generated using SQL SUM() function can be stored in a variable or temporary column referred as alias. The same approach can be used with SQL COUNT() function too.


To get SUM of total number of records in 'customer' table, the following SQL statement can be used:

Sample table: customer

SQL Code:

SELECT SUM(mycount)
FROM customer);



SQL SUM() and COUNT() with inner join

In the following example, we have discussed how SQL SUM and SQL COUNT function with the GROUP BY clause makes a join with SQL INNER JOIN statement. The data from a subquery can be stored in a temporary table or alias.

The data of these temporary tables can be used to manipulate data of another table. These two tables can be joined by themselves and to return a result.


To get (1) data of 'agent_code' and 'agent_name' from 'customer' table and ( 2) 'mycount' and 'mysum' from alias cus, generating from 'customer' table with following conditions -

1. 'mycount' and 'mysum' will come from alias cus,

2. alias 'cus' will be grouped based on agent_code,

3. customer and alias 'cus' will inner joined based on the same agent_code,

the following SQL statement can be used :

Sample table: customer

Sample table: agents

SQL Code:

SELECT ag.agent_code, ag.agent_name,cus.mycount,cus.mySUM
FROM agents ag
SELECT agent_code,COUNT(*) AS mycount,
SUM(opening_amt) AS mySUM
FROM Customer
GROUP BY agent_code) cus
ON cus.agent_code=ag.agent_code;


AGENT_CODE AGENT_NAME                                  MYCOUNT      MYSUM
---------- ---------------------------------------- ---------- ----------
A002       Mukesh                                            3      22000
A004       Ivan                                              3      25000
A007       Ramasundar                                        2      16000
A009       Benjamin                                          1       6000
A011       Ravi Kumar                                        1       5000
A012       Lucida                                            1       5000
A010       Santakumar                                        3      22000
A001       Subbarao                                          1       8000
A008       Alford                                            3      13000
A006       McDen                                             2       8000
A005       Anderson                                          3      19000
A003       Alex                                              2      12000

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 using GROUP BY
Next: Avg function

Follow us on Facebook and Twitter for latest update.