CASE statement leaving NULL-CodePudding

I am trying to make it so there are no NULLs in the JobTitle column. When I do it without at the CASE, I get two JobTitle columns (one for males and one for females) and some have NULL. I want to make it so there is just one column listing all the job titles then listing the total number of males/females next to that column in their own columns. (This is using the AdventureWorks db)

USE AdventureWorks2019 
GO 

select count(hre.gender) AS NumberOfFemales, JobTitle
into #FemalesPerJobTitle
from HumanResources.employee as hre                     
group by JobTitle, Gender
having gender = 'F';


SELECT COUNT(HRE.Gender) AS NumberOfMales, JobTitle
INTO #MalesPerJobTitle
FROM HumanResources.Employee AS HRE
GROUP BY JobTitle, Gender
HAVING gender = 'M';

SELECT FPJ.NumberOfFemales AS Females
    , MPJ.NumberOfMales AS Males
    , 
CASE
    WHEN MPJ.JobTitle IS NULL THEN FPJ.JobTitle
END AS JobTitle
FROM #FemalesPerJobTitle AS FPJ
FULL OUTER JOIN #MalesPerJobTitle AS MPJ
ON FPJ.JobTitle = MPJ.JobTitle

CodePudding user response：

SELECT 
  JobTitle,
  SUM(CASE WHEN gender = ‘M’ THEN 1 ELSE 0 END) AS Males,
  SUM(CASE WHEN gender = ‘F’ THEN 1 ELSE 0 END) AS Females
FROM HumanResources.Employee
GROUP BY JobTitle

You could probably put a COALESCE around the SUMs if it is returning any nulls and you want zeros instead

CodePudding user response：

It sounds like you want to produce a result set that

list all job titles, with
the count of men and women holding each title

If you have a table holding the complete list of job titles (which the database should since job title is/would seem to be a proper entity), I would use that as the source for the job title column.

But, if you don't have that, I'd do something like the following and use a derived table to give the the distinct set of job titles in both source tables:

select coalesce( f.NumberOfFemales , 0 ) Females  ,
       coalesce( m.NumberOfMales   , 0 ) Males    ,
       jt.JobTitle                       JobTitle 
from (       select JobTitle from #FemalesPerJobTitle
       UNION select JobTitle from #MalesPerJobTitle
     )                        jt
left join #FemalesPerJobTitle f  on f.JobTitle = jt.JobTitle
left join #MalesPerJobTitle   m  on m.JobTitle = jt.JobTitle

Another way to go about it (and probably easier for others to understand) would be to do something like this:

select JobTitle        = t.JobTitle,
       NumberOfFemales = sum( t.NumberOfFemales ) ,
       NumberOfMales   = sum( t.NumberOfMales   )
from (   select JobTitle,
                NumberOfFemales = NumberOfFemales,
                NumberOfMales   = 0
         from #FemalesPerJobTitle
       UNION ALL
         select JobTitle,
                NumberOfFemales = 0 ,
                NumberOfMales   = NumberOfMales
         from #MalesPerJobTitle
     ) t
group by t.JobTitle

Here, the derived table uses UNION ALL to not eliminate duplicates because (1) there shouldn't be any, (2) the query will be more efficient, and (3) the group by clause will take care of the roll-up.

CodePudding user response：

Here you go, I used CTE which makes more sense than temp tables here.

WITH Fcount AS 
(
    select count(hre.gender) AS NumberOfFemales, JobTitle
    from HumanResources.employee as hre
    WHERE gender = 'F'                     
    group by JobTitle
), Mcount AS 
(
    SELECT COUNT(HRE.Gender) AS NumberOfMales, JobTitle
    FROM HumanResources.Employee AS HRE
    WHERE gender = 'M'
    GROUP BY JobTitle
), titles CASE
(
   SELECT DISTINCT JobTitle FROM Fcount
   UNION ALL
   SELECT DISTINCT JobTitle FROM MCount
)
SELECT titles.JobTitle,
       FPJ.NumberOfFemales AS Females,
       MPJ.NumberOfMales AS Males
FROM titles 
LEFT JOIN Fcount AS FPJ ON titles.JobTitle = FPJ.JobTitle
LEFT JOIN Mcount AS MPJ ON titles.JobTitle = MPJ.JobTitle