Articles Cookbook
Back to list
T-SQL

How to Group by Two Columns in SQL

Database:

Operators:

GROUP BY

Problem:

You want to group your data by two columns so you can count some statistics.

Example:

In the order table, you have the columns order_date, product_id, customer_id, and number. You would like to count the number of products bought by each customer each day.

The order table looks like this:

order_dateproduct_idcustomer_idnumber
2020-11-25711
2020-11-251213
2020-11-265312
2020-11-26124
2020-11-26321
2020-11-261627
2020-11-26332
2020-11-27631

Solution:

SELECT
  order_date,
  customer_id,
  SUM(number) AS products_number
FROM order

The result is:

order_datecustomer_idproducts_number
2020-11-2632
2020-11-2731
2020-11-26212
2020-11-2514
2020-11-2612

Discussion:

To group by two columns, simply use GROUP BY with two columns. The column names should be listed after the GROUP BY keyword and separated by a comma. Groups will be created based on the values of both columns; for each pair of values, a separate group is created (e.g. ('2020-11-25', 1)). Look at the table below, where each group is presented in a different color:

order_datecustomer_idproduct_idnumber
2020-11-25171
2020-11-251123
2020-11-261532
2020-11-26214
2020-11-26231
2020-11-262167
2020-11-26332
2020-11-27361

If one or both columns have NULL values, these values are treated as a separate group (e.g., ('2020-11-26', NULL), (NULL, 5) or (NULL, NULL)).

On the other hand, if there are NULLs in a column on which we apply an aggregate function, the NULL values are simply omitted. (In this example, the aggregate function is SUM() and the column is number). If we had the number values 2, 1, and NULL for one of the groups, the SUM(number) would equal 3 (2 and 1 are added together, and NULL is omitted.)

Similarly, you could group by any number of columns – just write the column names in the GROUP BY clause and separate them with commas.

Recommended courses:

Recommended articles:

See also:

go to top