Friday, 8 November 2013

SQL GROUP BY Clause Synatax and Examples

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 ORDERBY 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 of GROUP BY Clause :

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.

Realistic Examples:
Let  us consider the following Table  EMP :

Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi
Analyst
1
20000
1002
David
Team Lead
2
35000
1003
Victory
Analyst
1
10000
1004
Tamatam
Team Lead
3
25000
1005
King
Analyst
2
15000
1006
William
Manager
3
50000
1007
Vikram
Analyst
2
180000
1008
Vivek
Team Lead
1
33000
1006
Smith
Team Lead
3
50000

Example-I :
Write a Query to find the count of Employees of various Designations (JOB) in each Department (DEPT_ID) from the above table EMP.
QUERY :
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 :


DEPT_ID
JOB
COUNT_OF_EMPs
1
Analyst
2
1
Team Lead
1
2
Analyst
2
2
Team Lead
1
3
Manager
1
3
Team Lead
2
Example-II :
Write a Query to find the count of Employees of Designation (JOB)='Analyst" in each Department (DEPT_ID) from the above table EMP.

QUERY :
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 :

JOB
DEPT_ID
COUNT_of_Analysts
Analyst
1
2
Analyst
2
2
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

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts