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

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

Course Preview