SQL Server Tutorial
In SQL Server, GROUP BY clause is used to summarizes the records into groups in the query using aggregate functions.
HAVING clause is used to further filter and restricts the result set to generate reports based on the condition.
,p>GROUP BY ALL, ALL keyword is used to display all groups including those excluded in WHERE clause.SELECT column_name1[, column_name2, ....] FROM table_name WHERE condition [GROUP BY [ALL] column_name | expression [, column_name | expression ...] HAVING filter_condition
ALL keyword is used to include the groups which do not meet the filter condition.
Let us consider the example 'Employee' database table.
To group by column Country and getting minimum and maximum salary for each groups.
SELECT Country, MIN(Salary) 'Minimum Salary', MAX(Salary) 'Maximum Salary' FROM Employee WHERE Salary > 4000 GROUP BY CountryOutput:
To group by column Country, DeptId and getting minimum and maximum salary for each groups.
SELECT Country, DeptId, MIN(Salary) 'Minimum Salary', MAX(Salary) 'Maximum Salary' FROM Employee WHERE Salary > 5000 GROUP BY Country, DeptIdOutput:
To group by column Country and getting average salary for each groups and filters the groups which are not having average salary is greater than 7000.
SELECT Country, AVG(Salary) 'Average Salary' FROM Employee WHERE Salary > 4000 GROUP BY Country HAVING AVG(Salary) > 7000Output:
To group by column Country, getting average salary for each groups and display's all group's in the result set.
Display's average salary as NULL value if not calculated for any groups.
SELECT Country, AVG(Salary) 'Average Salary' FROM Employee WHERE Salary >8000 GROUP BY ALL CountryOutput:
SQL Server Tutorial
Privacy Policy | Copyright2020 - All Rights Reserved. | Contact us | Report website issues in Github | Facebook page | Google+ page