SUM Function in SQL
The SUM is an aggregate function in SQL which returns the sum of integer column of the table.
Syntax of SUM Function
In the Structured Query Language, we use the SUM function with the columns of the table as shown in the following block:
In this syntax, we have to define the name and column of that table on which we want to perform the SUM function.
Example of SUM function
Here, we will create the new table through which we will perform the SUM function with the columns of the table:
The following shows the syntax to create the new table in SQL:
The following CREATE statement creates the Product_Details table for storing the price and quantity of products:
The following multiple INSERT queries insert the records of products with their selling and purchasing price into the Product_Details table:
The following SELECT statement displays the inserted records of the above Product_Details table:
| Product_ID | Product_Name | Product_Quantity | Purchasing_Price | Selling_Price | Release_Date | Category | Product_Rating |
|---|---|---|---|---|---|---|---|
| 104 | P1 | 10.250 | 945 | NULL | 2022-04-30 | Cloths | NULL |
| 202 | P4 | 15.500 | 45 | 75 | 2022-01-28 | Electrical | 5 |
| 103 | P2 | 18.250 | 25 | NULL | 2022-02-18 | Toys | 4 |
| 111 | P7 | 25.250 | 5 | 15 | 2021-12-25 | Cloths | 9 |
| 210 | P6 | 15.500 | 50 | 70 | 2021-10-15 | Electrical | NULL |
| 212 | P8 | 19.750 | 110 | 250 | 2022-01-28 | Cloths | 4 |
| 112 | P10 | 10.250 | 550 | 835 | 2022-04-11 | Toys | NULL |
Query 1: The following SELECT query uses the SUM function with the Product_Quantity column of the above Product_Details table:
Output:
| Total_sum_ofproductquantity |
|---|
| 114.75 |
Query 2: The following SELECT query uses the SUM function with the Selling_Price column of the above Product_Details table:
This query returns the sum of selling price products.
Output:
| Total_ofsellingpriceproducts |
|---|
| 1875 |
Query 3: The following SELECT query uses the SUM function with the Product_Rating column of the above Product_Details table:
This query returns the sum of values of Product_rating columns.
Output:
| Total_ofofproductrating |
|---|
| 22 |
SUM Function with WHERE clause
We can also use the WHERE clause with the SUM function which adds the values of filtered rows.
The syntax for using the SUM function with the WHERE clause is as follows:
Query 1: The following SELECT query uses the SUM function with WHERE clause in the above Product_Details table:
This query adds the qunatity of those products from above table whose product id is greater than 200.
Output:
| Total_ofproduct>200 |
|---|
| 50.75 |
Query 2: The following SELECT query uses the SUM function with WHERE clause in the above Product_Details table:
This query adds the purchasing price of those products which are release on 2022-01-28.
Output:
| Total_of_purchasingprice |
|---|
| 325 |
SUM Function with DISTINCT clause
We can also use the DISTINCT clause with the SUM function which adds the distinct values of the column from the table.
The syntax for using the SUM function with the DISTINCT clause is as follows:
Query 1: The following SELECT query uses the SUM function with the DISTINCT clause in the above Product_Details table:
This query adds the quantity of distinct values of Product_quantity column.
Output:
| Total_ofdistinctproduct |
|---|
| 89 |
Query 2: The following SELECT query uses the SUM function with the DISTINCT clause of the above Product_Details table:
Output:
| Total_ofdistinctproductrating |
|---|
| 18 |
SUM Function with GROUP BY clause
We can also use the GROUP BY clause with the SUM function which adds the values which exist in the same group.
The syntax for using the SUM function with the GROUP BY clause is as follows:
Query 1: The following SELECT query uses the SUM function with the GROUP BY clause in the above Product_Details table:
This query adds the quantity of same category.
Output:
| Category | Total_ofsamegroup |
|---|---|
| Cloths | 55.25 |
| Toys | 28.5 |
| Electrical | 31 |