SQL Basics | DML: Data Manipulation Language | GROUP BY
The GROUP BY clause is used to group rows that have the same value.
Returning to aggregate functions, suppose you calculated all Sales with the SUM command. If you want to calculate Sales for each Store_Name, you need to do two things. First, select the store_name and Sales fields. Second, calculate sales for each store_name. The syntax is as follows.
GROUP BY syntax
SELECT "field1", SUM("field2")
FROM "table_name"
GROUP BY "field1";
For example:
store_information table
| store_name | sales | txn_date |
|---|---|---|
| Los Angeles | 1500 | Jan-05-2018 |
| San Diego | 250 | Jan-07-2018 |
| Los Angeles | 300 | Jan-08-2018 |
| Boston | 700 | Jan-08-2018 |
Enter the following command.
SELECT store_name, SUM(sales)
FROM store_Information
GROUP BY store_name;
The result is as follows.
| store_name | SUM(sales) |
|---|---|
| Los Angeles | 1800 |
| San Diego | 250 |
| Boston | 700 |
When you select more than one field and at least one of them includes a function calculation, you must use GROUP BY. In this case, check the other fields together with GROUP BY. In other words, after identifying the field that includes the function, put the other selected field in the GROUP BY clause.