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

By Rajesh Vaddi · October 2023

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.