SQL GROUP BY Clause

The SQL GROUP BY statement is used together with the SQL aggregate functions to group the retrieved data by one or more columns. The GROUP BY concept is one of the most complicated concepts for people new to the SQL language and the easiest way to understand it, is by example.

Remember we used the SUM keyword to calculate the total sales for all stores? What if we want to calculate the total sales for each store? Well, we need to do two things: First, we need to make sure we select the store name as well as total sales. Second, we need to make sure that all the sales figures are grouped by stores. The corresponding SQL syntax is,

SELECT "column_name1", SUM("column_name2")

FROM "table_name"

GROUP BY "column_name1";

Let's illustrate using the following table,

Table Store_Information

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999
Los Angeles	300	Jan-08-1999
Boston	700	Jan-08-1999

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	1800
San Diego	250
Boston	700