Group BY
select dept,salary from employee
group by dept,salary
Group by clause arrange the rows in group with unique combination. when we select column only with group by clause then it gives unique combination of value and work similar as distinct clause.
Group by clause with aggregate function:
select dept,avg(salary) avg_sal,max(salary) max_sal,min(salary) min_sal,sum(salary) sal,count(salary) cnt from employee group by dept
GROUP BY GROUPING SETS ( )
The GROUPING SETS option gives you the ability to combine multiple GROUP BY clauses into one GROUP BY clause. The results are the equivalent of UNION ALL of the specified groups.
select GeographyKey,count(*) from [dbo].[DimCustomer] group by GeographyKey
select Gender,count(*) from [dbo].[DimCustomer] group by Gender
select GeographyKey,count(CustomerKey) count,Gender from [dbo].[DimCustomer]
group by grouping sets(GeographyKey,gender)
GROUP BY ROLLUP
Creates a group for each combination of column expressions. In addition, it "rolls up" the results into subtotals and grand totals. To do this, it moves from right to left decreasing the number of column expressions over which it creates groups and the aggregation(s).
select EnglishOccupation,EnglishEducation,count(*) from [dbo].[DimCustomer] group by
rollup(EnglishOccupation,EnglishEducation) order by EnglishOccupation,EnglishEducation
ROUP BY ROLLUP (col1, col2, col3, col4) creates groups for each combination of column expressions in the following lists.
- col1, col2, col3, col4
- col1, col2, col3, NULL
- col1, col2, NULL, NULL
- col1, NULL, NULL, NULL
- NULL, NULL, NULL, NULL -Grand Total
GROUP BY CUBE ( )
GROUP BY CUBE creates groups for all possible combinations of columns. For GROUP BY CUBE (a, b) the results has groups for unique values of (a, b), (NULL, b), (a, NULL), and (NULL, NULL).
select EnglishOccupation,EnglishEducation,Gender,count(*) from [dbo].[DimCustomer] group by
cube(EnglishOccupation,EnglishEducation,Gender) order by EnglishOccupation,EnglishEducation,Gender
SELECT statement
SELECT list:
- Vector aggregates. If aggregate functions are included in the SELECT list, GROUP BY calculates a summary value for each group. These are known as vector aggregates.
- Distinct aggregates. The aggregates AVG (DISTINCT column_name), COUNT (DISTINCT column_name), and SUM (DISTINCT column_name) are supported with ROLLUP, CUBE, and GROUPING SETS.
WHERE clause:
- SQL removes Rows that do not meet the conditions in the WHERE clause before any grouping operation is performed.
HAVING clause:
- SQL uses the having clause to filter groups in the result set.
ORDER BY clause:
- Use the ORDER BY clause to order the result set. The GROUP BY clause does not order the result set.
NULL values:
- If a grouping column contains NULL values, all NULL values are considered equal, and they are collected into a single group.