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.

Example:

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(SELECT COUNT( * ) AS mycount
FROM customer);

Output:

SUM(MYCOUNT)
------------
          25

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.

Example:

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
INNER JOIN (
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;

Output:

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.