CODEWITHSUNDEEP
sql
user6246171
user6246171

Reputation:

How can you have two COUNT queries GROUPED BY the same column in SQL?

I have two table named, one named Employees, and one named Shop. The Employees table holds the gender of all employees, while the Shop table has the name of the store that each employee works at.

I need to count the number of both male and female employees and group them by the store they work at. For example, the wanted outcome would look like this:

FemaleEmployees MaleEmployees Store 1 2 Ed's Hardware 3 4 Grocery store

So far I have this:

SELECT COUNT(Gender) AS FemaleEmployees, StoreName AS Store
FROM Employees 
JOIN Store
ON Employees.EmployeeID = Store.EmployeeID
Where Gender = 'F'
GROUP BY StoreName;

SELECT COUNT(Gender) AS MaleEmployees, StoreName AS Store
FROM Employees
JOIN Store
ON Employees.EmployeeID = Store.EmployeeID
Where Gender = 'M'
GROUP BY StoreName;

Which obviously gives the needed answer in two select queries, though I need it to be in a single query. I just need help on getting the two into a single query format.

I have experimented with using two subquerys, but so far nothing has worked. I know how to get the answer, just not in the needed format. Would appreciate it if someone could help me figure this one out.

Upvotes: 2

Views: 86

Answers (4)

Shivanand Baste
Shivanand Baste

Reputation: 84

most of above answer always get count of gender 1 or 0 only...not get actual count.. SO GETTING ACTUAL COUNT YOU CAN DO THIS..

SELECT (CASE WHEN GENDER = 'M' THEN COUNT(GENDER) ELSE 0 END) as MaleCount, (CASE WHEN GENDER = 'F' THEN COUNT(GENDER) ELSE 0 END) AS FemaleCount, Storename AS Store FROM Employees JOIN Store ON Employees.EmployeeID = Store.EmployeeID GROUP BY StoreName;

Upvotes: 0

Hila DG
Hila DG

Reputation: 738

you need to use "case" statement to differentiate between the cases, like this: SELECT SUM (SELECT CASE WHEN GENDER = 'M' THEN 1 ELSE 0 END) as MaleCount, SUM (SELECT CASE WHEN GENDER = 'F' THEN 1 ELSE 0 END) AS FemaleCount, Storename AS Store FROM Employees JOIN Store ON Employees.EmployeeID = Store.EmployeeID

GROUP BY StoreName;

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

Another way using COUNT

SELECT COUNT(CASE WHEN Gender='F' THEN 1 END) As FemaleEmployees,
       COUNT(CASE WHEN Gender='M' THEN 1 END) As MaleEmployees,
       StoreName AS Store
FROM Employees 
JOIN Store
ON Employees.EmployeeID = Store.EmployeeID
Where Gender in ( 'F','M' )
GROUP BY StoreName;

Filter the Gender in Where clause to make the query little efficient..

Upvotes: 3

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

You can do this

SELECT SUM(CASE WHEN Gender='F' THEN 1 ELSE 0 END) As FemaleEmployees,
       SUM(CASE WHEN Gender='M' THEN 1 ELSE 0 END) As MaleEmployees,
       storeName
FROM Employees 
JOIN Store
ON Employees.EmployeeID = Store.EmployeeID
GROUP BY storeName

Upvotes: 4

Related Questions