MSSQL table query issue

Question

can someone show me the way to get correct data results of using MSSQL between two tables as shown below

**Person_Table**          **Departemnt_Table**
===========                 ================
person_id                   depatment_id
age                         department name 
gender                         
department_id

And, i want to get the result as following columns:

Department Names, 
Number under 20 aged Males in each department, 
number of under 20 aged Females in each department, 
total number of Males in each department, 
total number of Females in each department and, 
Total number of Persons in each department

The example result should appears like this

DepartmentName|(Under20)Male|(Under20)Female|Total(M)| Total(F)| Grand Total
Department1   |    1        |  3            |  1     |  3      |   4  
Department2   |    3        |  1            |  15    |  1      |   16
Department3   |    0        |  0            |  0     |  0      |   0

Answer 1

DECLARE @Person TABLE(person_id int null,
age  int null,
gender   varchar(10) null,
department_id INT null)

DECLARE @Department TABLE(
department_id INT, DepartmentName Varchar(200))

INSERT INTO @Department VALUES (1,'A')
INSERT INTO @Department VALUES (2,'B')
INSERT INTO @Department VALUES (3,'C')
INSERT INTO @Department VALUES (4,'D')

INSERT INTO @Person VALUES (1,19,'M',1)
INSERT INTO @Person VALUES (2,12,'F',1)
INSERT INTO @Person VALUES (3,30,'M',1)
INSERT INTO @Person VALUES (4,40,'F',1)

INSERT INTO @Person VALUES (5,20,'M',2)
INSERT INTO @Person VALUES (6,12,'F',2)
INSERT INTO @Person VALUES (7,30,'M',2)
INSERT INTO @Person VALUES (8,40,'F',2)

INSERT INTO @Person VALUES (9,20,'M',3)
INSERT INTO @Person VALUES (10,12,'F',3)
INSERT INTO @Person VALUES (11,30,'M',3)
INSERT INTO @Person VALUES (12,40,'F',3)

INSERT INTO @Person VALUES (13,20,'M', 4)
INSERT INTO @Person VALUES (14,12,'F',4)
INSERT INTO @Person VALUES (15,30,'M',4)
INSERT INTO @Person VALUES (16,17,'F',4)


Select  D.DepartmentName ,
SUM([(Under20)Male]) [(Under20)Male],
SUM([(Under20)Female])[(Under20)Female],
SUM([Total(M)]) [Total(M)],
SUM([ Total(F)]) [ Total(F)],
SUM([Grand Total]) [Grand Total]
FROM

(

Select  department_id,COUNT(*) as  [(Under20)Male],0  [(Under20)Female],0 [Total(M)],0 [ Total(F)],0 [Grand Total] From @Person  WHERE age<20 and gender='M' group by department_id
Union all
Select  department_id,0 as  [(Under20)Male],COUNT(*)  [(Under20)Female],0 [Total(M)],0 [ Total(F)],0 [Grand Total] From @Person  WHERE age<20 and gender='F' group by department_id
Union all
Select  department_id,0 as  [(Under20)Male],0  [(Under20)Female],COUNT(*) [Total(M)],0 [ Total(F)],0 [Grand Total] From @Person  WHERE gender='M' group by department_id
Union all
Select  department_id,0 as  [(Under20)Male],0  [(Under20)Female],0 [Total(M)],COUNT(*) [ Total(F)],0 [Grand Total] From @Person  WHERE  gender='F' group by department_id
union all
Select  department_id,0 as  [(Under20)Male],0  [(Under20)Female],0 [Total(M)],0 [ Total(F)],COUNT(*) [Grand Total] From @Person  group by department_id
)A

INNER JOIN
 @Department D

ON A.department_id=D.department_id
GROUP BY D.DepartmentName

Answer 2

You need to youse GROUP By clause with aggregate functions .

SELECT DT.DepartmentName,
        SUM(CASE WHEN PT.age < 20 AND PT.gender='MALE' THEN 1 ELSE 0 END) As [(Under20)Male]
       ,SUM(CASE WHEN PT.age < 20 AND PT.gender='FEMALE' THEN 1 ELSE 0 END) As [(Under20)FeMale],
       ,SUM(CASE WHEN PT.gender='MALE' THEN 1 ELSE 0 END) As [Total(M)]
       ,SUM(CASE WHEN PT.gender='FEMALE' THEN 1 ELSE 0 END) As [Total(F)]
       ,COUNT(*) As [Grand Total]   
FROM Departemnt_Table DT
INNER JOIN Person_Table PT ON DT.depatment_id = PT.depatment_id
GROUP BY DT.DepartmentName