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:
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 on Alias columns of a table.

Where clause should use only after From clause and before Group By clause of a table.

Thanks,TAMATAM