Using group by of two columns and count in sql

By | December 19, 2019

Here is explanation of Using group by of two columns and count in sql as below, lets say we have two table Emp and Dep.

Select * from [dbo].[tblDep]

id	name
1	IT
2	HR
3	Finance

Select * from [dbo].[tblEmp]

Empid	name	Gender	depId
1002	Jatin	male	1
1003	Nirmal	male	1
1004	Aditi	Female	1
1005	Abhi	male	2
1006	nisha	female	2
1007	dipti	female	2

so here we want group of male and female employee per department with count.

SELECT a, b, COUNT(a) FROM tbl GROUP BY a, b 
So query would be like,

here a = department Name, b = Gender

Select d.name, e.Gender, count(d.name)
from [dbo].[tblDep] d inner join [dbo].[tblEmp] e
on d.id = e.depId
group by d.name, e.Gender
order by d.name, e.Gender