# 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)

**Weekly Trends and Language Statistics**- Weekly Trends and Language Statistics