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