RSS Feed

SQL Group By

SQL Having Examples of SQL Group By and SQL Having Null in SQL Group By


It is a clause in SQL, which specifies how to report the output of the query. Allows one to define a subset of the values of a particular field and to apply an aggregate function to the subsets.

We normally use a GROUP BY clause in conjunction with an aggregate expression (like SUM, COUNT etc).

Example 1 of SQL Group BY

Calculate the total sales for each store in the following table

store_nameSalesDate
London$1500Jan-05-1999
San Diego$250Jan-07-1999
London$300Jan-08-1999
Boston$700Jan-08-1999


First, we need to make sure we select the store name as well as total sales.

SELECT store_name, SUM (Sales)
FROM Store_Information

Second, we need to make sure that all the sales figures are grouped by stores.

SELECT store_name, SUM (Sales)
FROM Store_Information
GROUP BY store_name

So the final query is:

SELECT store_name, SUM (Sales)
FROM Store_Information
GROUP BY store_name

The result is:

store_nameSUM(Sales)
London$1800
San Diego$250
Boston$700


Example 2 of SQL Group BY

SELECT COUNT (*) FROM t_state

The above statement returns the total number of rows in the table. We can use GROUP BY to count the number of offices in each state.

With GROUP BY, the table is split into groups by state, and COUNT (*) is applied to each group in turn.

SELECT state, COUNT (*)
FROM t_state
GROUP BY state;

Important points to remember:

Group by cannot use column aliasing. A GROUP BY clause must contain the column or expressions on which to perform the grouping operation. For example:

Incorrect way:

Select deptno as department, count (*) as cnt
From emp
Group by department

Correct way is:

Select deptno as department, count (*) as cnt
From emp
Group by deptno


What is the difference between the outputs of the following two queries?

Statement 1:

SELECT COUNT (*), SUM (comm)
FROM hr.employees;

Statement 2:

SELECT COUNT (comm), SUM (comm)
FROM hr.employees;

The COUNT (*) will count all rows in the table.

The COUNT (comm) will count only the number commission values that appear in the table. If there are any rows with a NULL commission, statement 2 will not count them.

Restriction on SELECT Lists with Aggregation

If any aggregation is used, then each element of a SELECT clause must either be aggregated or appear in a group-by clause. i.e. as a rule, when using GROUP BY and aggregate functions, any items in the SELECT list not used as an argument to an aggregate function must be included in the GROUP BY clause.