Problem:

How to use the GROUP BY clause on multiple columns in SQL?

Input:

Let’s assume we have a table called Orders with columns OrderID, CustomerID, ProductID, and OrderDate.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    OrderDate DATE
);

-- Inserting sample data
INSERT INTO Orders (OrderID, CustomerID, ProductID, OrderDate) VALUES
(1, 101, 201, '2023-01-01'),
(2, 101, 202, '2023-01-02'),
(3, 101, 201, '2023-01-02'),
(4, 102, 201, '2023-01-03'),
(5, 102, 203, '2023-01-03'),
(6, 102, 201, '2023-01-05'),
(7, 103, 202, '2023-01-05');

Select * from Orders;

Orders Table:

OrderID	CustomerID	ProductID	OrderDate
1	101	201	2023-01-01
2	101	202	2023-01-02
3	101	201	2023-01-02
4	102	201	2023-01-03
5	102	203	2023-01-03
6	102	201	2023-01-05
7	103	202	2023-01-05

From the above data, we want to know the count of orders made by each customer for each product.

Desired Output

CustomerID	ProductID	NumberOfOrders
101	201	2
101	202	1
102	201	2
102	203	1
103	202	1

Solution:

To get the count of orders made by each customer for each product, you can group by both CustomerID and ProductID.

SELECT 
    CustomerID,
    ProductID,
    COUNT(OrderID) AS NumberOfOrders
FROM 
    Orders
GROUP BY 
    CustomerID, ProductID;

Output:

CustomerID	ProductID	NumberOfOrders
101	201	2
101	202	1
102	201	2
102	203	1
103	202	1

Explanation

The above query groups the data by both CustomerID and ProductID columns and then counts the number of orders (using the OrderID column) for each unique combination of CustomerID and ProductID.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery
4. SQL_group_by

More SQL Questions

1. How to access previous row value in SQL?
2. How to exclude specific columns using select except?
3. How to transpose columns to rows in SQL?
4. How to select first row in each GROUP BY group?