0

UPDATE I determined a significantly more concise and computationally optimal way of expressing this statement. No subqueries no multiple joins.:

SELECT
month(hire_date) as month, 
sum(case when gender ="m" then 1 end) as male,
sum(case when gender ="f" then 1 end) as female,
count(*) as total 
FROM employees
GROUP BY MONTH(hire_date)

Using the MySQL Employees Sample Database, I am querying the same employees table twice via subquery and cross joining the results to get the pivot table below. I am looking to create a final column that would be the sum of mcount and fcount

Month,Gender,mcount,gender,fcount
'1','M','14310','F','9549'
'2','M','14659','F','9789'
'3','M','16200','F','10717'
'4','M','15363','F','10190'
'5','M','15545','F','10398'
'6','M','15000','F','10003'
'7','M','15260','F','10345'
'8','M','15250','F','10146'
'9','M','14632','F','9684'

I have tried to use subqueries again to create this column, but it's making it evident that my two GROUP BY sub-queries are the wrong way to go about building this pivot table.

SELECT mbm.month, mbm.gender, mbm.count as mcount, fbm.gender, fbm.count as fcount
FROM
(SELECT MONTH(hire_date) as Month, gender, COUNT(*) as count
FROM employees
WHERE gender = "M"
GROUP BY gender,MONTH(hire_date)
) as mbm

CROSS JOIN (SELECT MONTH(hire_date) as Month, gender, COUNT(*) as count
FROM employees
WHERE gender = "F"
GROUP BY gender,MONTH(hire_date)
) AS fbm on mbm.Month = fbm.Month
KLDavenport
  • 659
  • 8
  • 24
  • If male and female are the only values in the gender column, then the sum of the two pivoted column is basically the record count for the given month. – Shadow Nov 12 '18 at 06:30
  • cross joins don't have an ON clause, this should be written as a full outer join, in case there are months where no males or no females exist. As shadow alludes to, selecting the gender in a query that has only one gender in the where clause, is pointless. `select coalesce(mbm.month, fbm.month) as month, mbm.count as malecount, fbm.count as female count from ... full outer join ... on mbm.month = fbm.month` – Caius Jard Nov 12 '18 at 06:38
  • The new column I'm trying to get is indeed the sum for the given month, but what are you suggesting? – KLDavenport Nov 12 '18 at 06:40
  • Replace the ... with the subqueries. Subqueries simply need to SELECT month(), count() ... where gender = whatever). Remove to group by / select the year if needed – Caius Jard Nov 12 '18 at 06:44
  • Ps, your query says COUNT but you just said SUM. Decide which it is? – Caius Jard Nov 12 '18 at 06:45
  • I do want the count of all the occurrences of male then female, and the last column is the SUM of those two occurrences showing a total. – KLDavenport Nov 12 '18 at 06:47
  • Ignoring emulating a full outer join in MySQL for now, I've cleaned up the code per your advice, but how would I create the new final column? ```SELECT COALESCE(mbm.month, fbm.month) as month, mbm.count as malecount, fbm.count as femalecount from (SELECT MONTH(hire_date) as Month, COUNT(*) as count FROM employees WHERE gender = "M" GROUP BY gender,MONTH(hire_date) ) as mbm LEFT JOIN (SELECT MONTH(hire_date) as Month, COUNT(*) as count FROM employees WHERE gender = "F" GROUP BY gender,MONTH(hire_date) ) AS fbm on mbm.Month = fbm.Month``` – KLDavenport Nov 12 '18 at 07:00
  • doesn't seem elegant or efficient but I just added a third subquery left join of ``` LEFT JOIN (SELECT MONTH(hire_date) as Month, COUNT(*) as count FROM employees GROUP BY MONTH(hire_date) ) AS allbm``` – KLDavenport Nov 12 '18 at 07:18

1 Answers1

0

A significantly more concise and computationally optimal way of expressing this statement. No subqueries,no multiple joins, and easy to read:

SELECT
month(hire_date) as month, 
sum(case when gender ="m" then 1 end) as male,
sum(case when gender ="f" then 1 end) as female,
count(*) as total 
FROM employees
GROUP BY MONTH(hire_date)
KLDavenport
  • 659
  • 8
  • 24