Home » SQL » GROUP BY in SQL

GROUP BY in SQL

Overview: In this tutorial you will learn and understand GROUP BY clause in SQL with practical examples.

Introduction: GROUP BY in SQL

What is GROUP BY in SQL? : It is use to arrange the rows members by grouping (It is like a Pivot to grouping member to see aggregated value). The groups are determined by the columns that you specify in the GROUP BY clause. The queries that contain the GROUP BY clause are called grouped queries and only return a single row for every grouped item

What is the use of GROUP BY Clause?

Group by clause is helpful for analyst who do the reconciliation of numbers. If users want to extract aggregate numbers for particular information or specific data set, they need to use Group by clause. Like total cost of specific employee, profit/loss for one country etc ….

Syntax:

SELECT
      select_list
FROM
      table_name
GROUP BY
      column_name1,
      column_name2, ..

Example #1: GROUP BY in SQL for two columns

In our customer table we have below two columns

  • Product_Category
  • Product_Container

in below example try to get result for these two column applying Group By for both columns. It will show Product_Category only for individual Product_Container.  Performed as excel pivot.

SELECT
       Product_Category, 
       Product_Container
FROM
       Customers_Tbl
GROUP BY 
       Product_Category, Product_Container

So you can see below Result Product_Category and Product_Container now is grouped, that is why each Product_Category has single Product_Container information

Result:

GROUP BY Clause

Example #2: GROUP BY with the SUM function

GROUP BY in SQL use the Aggregate Function, If you are using aggregated function in query then you have to use GROUP BY clause, as aggregated function always work on GROUP BY Clauses.

SELECT
       Order Priority, 
       SUM([Order Quantity]) as order_Quantity
FROM
       Orders_Tbl
GROUP BY 
       Order Priority

As you can see in below Result Order_Quantity is summed up by “Order Priority”

Result:

GROUP BY Clause

Example #3: GROUP BY with the AVG function

GROUP BY in SQL use the Aggregate Function, If you are using aggregated function in query then you have to use GROUP BY clause, as aggregated function always work on GROUP BY Clauses.

Below example using AVG() function to show average of sales group by Order_Priority

SELECT        
     Order_Priority, 
     AVG(Sales) as Sales_avg 
FROM 
     Orders_Tbl
GROUP BY 
     Order_Priority

Result:

Example #4: GROUP BY using Having Clause

In below query display the records using group by of Product_Category with AVG of Margin which is greater then 40% with the help of HAVING clause.

Note:

In database margin in decimal that is why in query it shows decimal

SELECT    
      Product_Category,
      AVG (Product_Base_Margin) As Margin
FROM   
     Customers_Tbl
GROUP BY 
     Product_Category
HAVING 
     AVG(Product_Base_Margin) > 0.40

Result: