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:

-- Calculating the sum of the 'mycount' column
-- Result: Total count of rows in the subquery
SELECT SUM(mycount)
-- Subquery: Counting the total number of rows in the 'customer' table and aliasing the result as 'mycount'
FROM (SELECT COUNT(*) AS mycount FROM customer);

Explanation:

• (SELECT COUNT(*) AS mycount FROM customer): This inner query is used to calculate the count of rows in the 'customer' table. The COUNT(*) function counts all rows in the 'customer' table, and it is aliased as mycount.


• SELECT SUM(mycount): This outer query selects the sum of the mycount column from the result of the inner query. Since the inner query calculates the count of rows in the 'customer' table, the sum of mycount essentially represents the total count of rows in the 'customer' table.

Output:

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

SQL SUM() and COUNT() with inner join

Sample table: customer

Sample table: agents

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 :

SQL Code:

-- Selecting columns: ag.agent_code, ag.agent_name, cus.mycount, cus.mySUM
-- From the 'agents' table (aliased as 'ag')
SELECT ag.agent_code, ag.agent_name, cus.mycount, cus.mySUM
FROM agents ag
-- Joining the 'agents' table (aliased as 'ag') with a subquery (aliased as 'cus')
-- Subquery: Counting the total number of rows and summing 'opening_amt' for each 'agent_code' in the 'Customer' table
INNER JOIN (
  SELECT agent_code, COUNT(*) AS mycount, SUM(opening_amt) AS mySUM
  FROM Customer
  GROUP BY agent_code
) cus
-- Joining condition: Matching 'agent_code' between the 'agents' table and the subquery
ON cus.agent_code = ag.agent_code;

Explanation:

• SELECT ag.agent_code, ag.agent_name, cus.mycount, cus.mySUM: This is the main part of the SQL query. It selects four columns: 'agent_code' and 'agent_name' from the 'agents' table (ag), and 'mycount' and 'mySUM' from a subquery aliased as cus. The subquery calculates the count of customers (mycount) and the sum of the opening_amt column (mySUM) for each unique agent code.


• FROM agents ag: This specifies the source of the data for the main query, which is the 'agents' table. It aliases the table as ag.


• INNER JOIN (...) cus ON cus.agent_code = ag.agent_code: This part performs an inner join between the 'agents' table (ag) and a subquery aliased as cus. The subquery calculates aggregate statistics (COUNT and SUM) for each agent code from the 'Customer' table. The join is performed based on matching agent codes.


• The subquery (SELECT agent_code, COUNT(*) AS mycount, SUM(opening_amt) AS mySUM FROM Customer GROUP BY agent_code) cus: This subquery calculates aggregate statistics for each unique agent code from the 'Customer' table. It calculates the count of customers (mycount) and the sum of the opening_amt column (mySUM) for each agent code. The result is grouped by the 'agent_code' column.

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.