Remove From My Forums

sql query not displaying correctly

Question

User456628452 posted

i am doing sql count select query, where i want to display display data like this

Department	Female	Male
A	2	6
B	5	2
C	0	3

From the query i have written, i am getting data like this

Department	Female	Male
A	2	0
B	5	0
C	0	3
A	0	6
B	0	2

SELECT    c.Name,  count(Staff.StaffId) as Male, 0 as Female
FROM            Registrations AS e INNER JOIN
                         Branch AS c ON e.RegistrationID = c.RegistrationID 
WHERE   (Staff.GenderTypeID = 1)
GROUP BY   c.Name

UNION ALL

SELECT    c.Name, 0  as Male, count(Staff.StaffId) as Female
FROM            Registrations AS e INNER JOIN
                         Branch AS c ON e.RegistrationID = c.RegistrationID 
WHERE   (Staff.GenderTypeID = 2)
GROUP BY   d.Name

Tuesday, December 3, 2019 4:54 AM

Answers

User283571144 posted

Hi marya,

According to your sql query, I suggest you could try to use group by to achieve your requirement.

More deails, you could refer to below query:

Select re.Department, SUM(re.Female), SUM(re.Male)  from(

SELECT    c.Name,  count(Staff.StaffId) as Male, 0 as Female
FROM            Registrations AS e INNER JOIN
                         Branch AS c ON e.RegistrationID = c.RegistrationID 
WHERE   (Staff.GenderTypeID = 1)
GROUP BY   c.Name

UNION ALL

SELECT    c.Name, 0  as Male, count(Staff.StaffId) as Female
FROM            Registrations AS e INNER JOIN
                         Branch AS c ON e.RegistrationID = c.RegistrationID 
WHERE   (Staff.GenderTypeID = 2)
GROUP BY   d.Name) 
As Re group by  re.Department

Result:

Best Regards,

Brando

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Tuesday, December 3, 2019 7:19 AM

Answered by:

sql query not displaying correctly

Question

Answers