login

Subscribe to SQL Tutorial website Rss Feed
 » SQL Tutorial » SQL GROUP BY

SQL Group By

SQL GROUP BY is used to divide a database table into groups based on group columns. For each group you can apply aggregate functions such as SUM, AVG, MIN, MAX and COUNT to output the summary information.

Let’s take a look at several examples of using SQL GROUP BY to see how it works.

Suppose you want to find the total money of every sale order you have in order details table. You can use SQL GROUP BY and SUM function to do so. Here is the query:

SELECT orderID, SUM(unitPrice * quantity)
FROM order_details
GROUP BY orderID

Here is the excerpt of the result

orderID  SUM(unitPrice * quantity)
-------  -------------------------
  10248                   440.0000
  10249                  1863.4000
  10250                  1813.0000
  10251                   670.8000
  10252                  3730.0000
  10253                  1444.8000
  10254                   625.2000
  10255                  2490.5000
  10256                   517.8000
  10257                  1119.9000
  10258                  2018.6000
  10259                   100.8000
  10260                  1746.2000

SQL engine first looks at the GROUP BY clause and groups the table into groups based on the order identity. Then SQL engine computes the sum of unitPrice column multiple with quantity column for each group.

You can use SQL GROUP BY without the aggregate functions. At this time SQL GROUP BY acts like SELECT DISTINCT to distinguish all records based on group columns. Here is the query:

SELECT orderID, SUM(unitPrice * quantity)
FROM order_details
GROUP BY orderID

Here is the excerpt of the result

orderID  SUM(unitPrice * quantity)
-------  -------------------------
  10248                   440.0000
  10249                  1863.4000
  10250                  1813.0000
  10251                   670.8000
  10252                  3730.0000
  10253                  1444.8000
  10254                   625.2000
  10255                  2490.5000
  10256                   517.8000
  10257                  1119.9000
  10258                  2018.6000
  10259                   100.8000
  10260                  1746.2000

You can use SQL GROUP BY clause together with SQL ORDER BY clause to sort the output result. For example you can sort the total sale of all sale orders in descending order as the following query:

SELECT OrderID, SUM(unitPrice * quantity) total
FROM Order_details
GROUP BY OrderID
ORDER BY total DESC

Here is the excerpt of the result

OrderID     total  
-------  ----------
  10865  17250.0000
  11030  16321.9000
  10981  15810.0000
  10372  12281.2000
  10424  11493.2000
  10817  11490.7000
  10889  11380.0000
  10417  11283.2000
  10897  10835.2400
  10353  10741.6000

In this tutorial, you’ve learned how to use SQL GROUP BY to divide records in a database table into groups and apply aggregate function for each group to produce summarization output. In the next tutorial, you will learn HAVING clause which is related to GROUP BY, let’s move on.

Read On