MySQL SUM() function with group by

SUM() function with group by

MySQL SUM() function retrieves the sum value of an expression which has undergone a grouping operation by GROUP BY clause.

Example:

Sample table: purchase

Code:

-- This query calculates the total cost for each category in the 'purchase' table.
SELECT cate_id, SUM(total_cost)
-- This statement selects the category ID (cate_id) and calculates the sum of 'total_cost' for each category.
FROM purchase            
-- This part of the query specifies the table from which data is being retrieved, which is 'purchase'.
GROUP BY cate_id;
-- This clause groups the results by the 'cate_id' column, so that the total cost can be calculated for each category separately.

Explanation:

• The purpose of this SQL query is to compute the total cost for each category in the 'purchase' table.


• SELECT cate_id, SUM(total_cost): This part of the query selects two columns: cate_id and the sum of total_cost for each category. The SUM function adds up all the 'total_cost' values for each group of cate_id.


• FROM purchase: This part specifies the table from which the data is being selected, which is the 'purchase' table.


• GROUP BY cate_id: This clause groups the results by the cate_id column. It ensures that the total cost is calculated for each unique category separately.


• The query will return a list of category IDs along with the total cost incurred for each category in the 'purchase' table. This provides insights into the overall expenditure for each product category.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

The above MySQL statement returns the sum of 'total_cost' from purchase table for each group of category ('cate_id') .

Output:

mysql> SELECT cate_id,SUM(total_cost)
    -> FROM purchase            
    -> GROUP BY cate_id;
+---------+-----------------+
| cate_id | SUM(total_cost) |
+---------+-----------------+
| CA001   |         1725.00 | 
| CA002   |          965.00 | 
| CA003   |          900.00 | 
+---------+-----------------+
3 rows in set (0.00 sec)

Previous: SUM()
Next: VAR_POP()