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_name	Sales	Date
London	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
London	$300	Jan-08-1999
Boston	$700	Jan-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_name	SUM (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.