Group by clause | Group by clause command in sql - sql - sql tutorial - learn sql




 group by clause
  • The usage of SQL GROUP BY clause is, to divide the rows in a table into smaller groups.
  • The GROUP BY clause is used with the SQL SELECT statement.
  • The grouping can happen after retrieves the rows from a table.
  • When some rows are retrieved from a grouped result against some condition, that is possible with HAVING clause.
  • The GROUP BY clause is used with the SELECT statement to make a group of rows based on the values of a specific column or expression. The SQL AGGREGATE function can be used to get summary information for every group and these are applied to an individual group.
  • The WHERE clause is used to retrieve rows based on a certain condition, but it cannot be applied to grouped result.
  • In an SQL statement, suppose you are using GROUP BY, if required you can use HAVING instead of WHERE, after GROUP BY.
SQL count group by

Syntax:

SELECT <column_list> 
FROM < table name >
WHERE <condition>GROUP BY <columns> 
[HAVING] <condition>;

Examples:

  • We use the following table for our examples.
SQL Group by Operator

Table Store_Information

Store_Name Product_ID Sales Txn_Date
Los Angeles 1 1500 Jan-05-1999
Los Angeles 2 500 Jan-05-1999
San Diego 1 250 Jan-07-1999
Los Angeles 1 300 Jan-08-1999
Boston 1 700 Jan-08-1999
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example 1: GROUP BY a single column

  • We want to find total sales for each store. To do so, we would key in,
SELECT Store_Name, SUM(Sales)
FROM Store_Information
GROUP BY Store_Name;

Result:

Store_Name SUM(Sales)
Los Angeles 2300
San Diego 250
Boston 700
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example 2: GROUP BY multiple columns

  • In Example 1, there is only one column associated with GROUP BY. It is possible to have two or more columns associated with GROUP BY.
  • We want to find total sales for each product at each store. To do so, we would key in,
SELECT Store_Name, Product_ID, SUM(Sales)
FROM Store_Information
GROUP BY Store_Name, Product_ID;

Result:

Store_Name Product_ID SUM(Sales)
Los Angeles 1 1800
Los Angeles 2 500
San Diego 1 250
Boston 1 700

Example 3: GROUP BY multiple columns and multiple functions

  • We want to find total sales and the average sales for each product at each store. To do so, we would key in,
SELECT Store_Name, Product_ID, SUM(Sales), AVG(Sales)
FROM Store_Information
GROUP BY Store_Name, Product_ID;

Result:

Store_Name Product_ID SUM(Sales) AVG(Sales)
Los Angeles 1 1800 900
Los Angeles 2 500 500
San Diego 1 250 250
Boston 1 700 700

Example 4: Group by month / date / week

  • A common use of the GROUP BY function is on a time period, which can be month, week, day, or even hour. This type of query is often combined with the ORDER BY keyword to provide a query result that shows a time series.
  • For example, to find total daily sales from Store_Information, we use the following SQL:
SELECT Txn_Date, SUM(Sales)
FROM Store_Information
GROUP BY Txn_Date
ORDER BY Txn_Date;

Result:

Txn_Date SUM(Sales)
Jan-05-1999 2000
Jan-07-1999 250
Jan-08-1999 1000

Exercises

  • For these exercises, assume we have a table called Region_Sales with the following data:

Table Region_Sales

Region Year Orders Total_Sales
West 2013 1560 325000
West 2014 1820 380000
North 2013 790 148000
North 2014 995 185000
East 2013 1760 375000
East 2014 2220 450000
South 2013 1790 388000
South 2014 1695 360000

SQL Group by Example 1

SQL Having Example 1

SQL Group by Example 2

SQL Having Example 2

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Group by clause | Group by clause command in sql