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