SQL Interview Questions

The GROUP BY clause in SQL is used to group rows that have the same values in specified columns into aggregated data, such as sums, averages, counts, and other statistics. It's typically used with aggregate functions like COUNT, SUM, AVG, MAX, and MIN to perform calculations on each group of data.

Syntax

SELECT column1, aggregate_function(column2)
FROM table
WHERE condition
GROUP BY column1;

Steps to Use GROUP BY

1. Select the Columns: Choose the columns you want to display in the result set. These typically include the column(s) you are grouping by and the aggregate functions.
2. Apply Aggregate Functions: Use aggregate functions to perform calculations on each group.
3. Group by Columns: Specify the column(s) you want to group by in the GROUP BY clause.
4. Optional - Filter Rows: Use the WHERE clause to filter rows before grouping.
5. Optional - Filter Groups: Use the HAVING clause to filter groups after grouping.

Examples

Basic Example

Suppose we have a table sales with the following structure:

sales
+----+---------+--------+
| id | product | amount |
+----+---------+--------+
| 1  | A       | 100    |
| 2  | B       | 150    |
| 3  | A       | 200    |
| 4  | B       | 100    |
| 5  | C       | 50     |
+----+---------+--------+

To find the total sales amount for each product:

SELECT product, SUM(amount) AS total_sales
FROM sales
GROUP BY product;

Result:

+---------+-------------+
| product | total_sales |
+---------+-------------+
| A       | 300         |
| B       | 250         |
| C       | 50          |
+---------+-------------+

Grouping with Multiple Columns

Suppose we have a table orders with the following structure:

orders
+----+---------+--------+---------+
| id | product | amount | country |
+----+---------+--------+---------+
| 1  | A       | 100    | USA     |
| 2  | B       | 150    | USA     |
| 3  | A       | 200    | Canada  |
| 4  | B       | 100    | Canada  |
| 5  | C       | 50     | USA     |
+----+---------+--------+---------+

To find the total sales amount for each product in each country:

SELECT product, country, SUM(amount) AS total_sales
FROM orders
GROUP BY product, country;

Result:

+---------+---------+-------------+
| product | country | total_sales |
+---------+---------+-------------+
| A       | USA     | 100         |
| A       | Canada  | 200         |
| B       | USA     | 150         |
| B       | Canada  | 100         |
| C       | USA     | 50          |
+---------+---------+-------------+

Using HAVING Clause

To filter groups based on the aggregated data, use the HAVING clause. For example, to find products with total sales greater than 100:

SELECT product, SUM(amount) AS total_sales
FROM sales
GROUP BY product
HAVING SUM(amount) > 100;

Result:

+---------+-------------+
| product | total_sales |
+---------+-------------+
| A       | 300         |
| B       | 250         |
+---------+-------------+

Summary

• GROUP BY Clause: Groups rows that have the same values in specified columns.
• Aggregate Functions: Perform calculations on each group.
• HAVING Clause: Filters groups based on aggregate function results.
• Use Cases: Summarizing data, counting occurrences, finding averages, etc.

By using the GROUP BY clause effectively, you can perform powerful data aggregation and analysis in SQL.