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:

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

Desired Output

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:

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?