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

• 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.

Syntax:

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

Examples:

• We use the following table for our examples.

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 Group by 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