How to use GROUP BY on multiple columns of a table in SQL
- How-Tos FAQs
- December 17, 2018
Let us consider the below table to see how to GROUP BY on multiple columns of a table in SQL. The GROUP BY clause is used to group the rows with the same value in one or many columns. It is often used with aggregate functions like SUM, MAX, MIN, AVG, COUNT, etc.,
There is data of orders placed by different customers on different dates.
CREATE TABLE orders (
order_id INT,
customer_name VARCHAR(100),
order_date DATETIME,
total_orders INT
);
INSERT INTO orders
SELECT 1,'Jack', '2019-11-24', 2 UNION ALL
SELECT 1,'Jack', '2020-02-03', 5 UNION ALL
SELECT 1,'Jack', '2020-02-03', 9 UNION ALL
SELECT 2, 'Rose', '2019-05-06', 8 UNION ALL
SELECT 2, 'Rose', '2020-05-07', 19 UNION ALL
SELECT 2, 'Rose', '2020-06-02', 13 UNION ALL
SELECT 3,'John', '2018-02-03', 45 UNION ALL
SELECT 3,'John', '2019-02-03', 50 UNION ALL
SELECT 3,'John', '2019-03-03', 20 UNION ALL
SELECT 3,'John', '2020-03-08', 54 UNION ALL
SELECT 3,'John', '2020-06-01', 66 If we group this data according to the customer only, then all rows of a particular customer will be combined into one row.
-- GROUP BY using only one column
SELECT customer_name,
SUM(total_orders) total_orders
FROM orders
GROUP BY customer_name
ORDER BY customer_name
-- There is just one row for each customer
customer_name total_orders
------------------ ------------
Jack 16
John 235
Rose 40If we group the data according to the customer and the year of the order date, then all rows of a particular customer will be further subdivided according to the year of the order date. So, there will be one row for each combination of customer and their year of the order date.
-- GROUP BY using multiple columns
SELECT customer_name,
YEAR(order_date) order_year,
SUM(total_orders) total_orders
FROM orders
GROUP BY customer_name, YEAR(order_date)
ORDER BY customer_name, order_year
-- There is one row for each unique combination of customer and year of order date
customer_name order_year total_orders
----------------- ----------- ------------
Jack 2019 2
Jack 2020 14
John 2018 45
John 2019 70
John 2020 120
Rose 2019 8
Rose 2020 32And that’s how to use GROUP BY on multiple columns of a table in SQL!
Up Next:
Read How to remove duplicate rows from a table in SQL server
