Friday, 8 November 2013

How to use GROUP BY Clause in SQL Server

SQL Server - GROUP BY Clause
GROUP BY clause is used to divide a table into logical groups and calculate aggregate statistics for each group.

Important properties of GROUP BY clause are as below.
GROUP BY clause appears after the WHERE clause and before the ORDER BY clause. 
  • We can group columns or derived columns. 
  • Columns from the input table can appear in an aggregate query's SELECT clause only when they are also included in the GROUP BY clause. 
  • Group BY expression must match the SELECT expression exactly. 
  • If you specify multiple grouping columns in the GROUP BY clause to nest groups, data is summarized at the final specified group. 
  • If WHERE clause is used in the query containing a GROUP BY clause, Rows are eliminate 
  • First which does not satisfy where condition and then grouping occurs. 
  • You cannot use column alias in the GROUP BY clause but table aliases are allowed.
Syntax  :
SELECT Columns
FROM Table
[WHERE Search_condition]
GROUP BY Grouping_columns
[HAVING Search_condition]
[ORDER BY Sort_columns]

Here…
Columns and grouping columns are one or more comma separated column names.
Table is a name of table that contains columns and Grouping_columns.
Search_condition is a valid sql expression.
Sort_columns are one or more column name of specified table.

Consider the following Table  EMP :


Example-I :
A Query to find the count of Employees of various Designations (JOB) in each Department (DEPT_ID) from the above table EMP.

SELECT DEPT_ID,JOB, COUNT(*) AS COUNT_OF_EMPS FROM EMP GROUP BY DEPT_ID  ,JOB ORDER BY DEPT_ID

OR we can write as follows

SELECT DEPT_ID,JOB, COUNT(EMP_ID) AS COUNT_OF_EMPS FROM EMP GROUP BY DEPT_ID  ,JOB ORDER BY DEPT_ID
Output :


Example-II :
A Query to find the count of Employees of Designation (JOB)='Analyst" in each Department (DEPT_ID) from the above table EMP.

SELECT  JOB,DEPT_ID,COUNT(EMP_ID) AS COUNT_of_Analysts FROM EMP WHERE JOB='Analyst' GROUP BY JOB,DEPT_ID ORDER BY DEPT_ID

Output :

Notes :
Where clause can be use only on Existing columns of a table.
Where clause cannot be use only on Alias columns of a table.
Where clause should use only after From cluase and before Group By clause of a table.

Thanks,

TAMATAM

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts