user3821879 user3821879 - 1 month ago 6
SQL Question

how to combine SQL queries in columns

I need rows of result set into columns. I currently have written the query :

SELECT COUNT(*) AS Male FROM add_faculty_override
WHERE gender='Male'
UNION
SELECT COUNT(*) AS Female FROM add_faculty_override
WHERE gender='Female';


This gives me the result as :

male
3 //total number of males
5 //total number of females


What I'd like to have instead as the result set is :

male female
3 5

Answer

You can do this with a CASE statement:

SELECT COUNT(CASE WHEN gender='Male' THEN 1 END) AS Male
     , COUNT(CASE WHEN gender='Female' THEN 1 END) AS Female
FROM add_faculty_override

If you don't really need both results on one row then you can simply GROUP:

SELECT Gender, COUNT(*) AS Gender_Count
FROM add_faculty_override
GROUP BY Gender
Comments