Pivot not returning aggregate (SQL Adventureworks)-CodePudding

I am using the Adventureworks sample database (HumanResources.Employee table is the database).

For simplicity I will take a sample of the table:

HireDate       Gender        SalariedFlag
-----------------------------------------
2014-01-01         M              1
2015-01-30         F              1
2014-01-30         M              1
2014-02-12         F              0
2014-03-11         F              1 and so on

Code:

SELECT
    YEAR(hiredate), F, M 
FROM
    HumanResources.employee
PIVOT
    (sum(SalariedFlag) 
         FOR gender IN ([F], [M])
    ) AS gg
  -- Unable to use sum though since its bit field

Expected output:

Year      F     M
-------------------
2014      1     2    # count(SalariedFlag) 
2015      1     0    # count(SalariedFlag)

But I really get:

No name     F               M
-------------------------------
2014         0              1
2015         1              0
2014         0              1
2014         1              0
2014         1              0

and so on.

So basically in output its not considering the salariedflag column at all, it is simply returning 1 in F if the person was female and 1 in M if person was male.

What am I doing wrong?

CodePudding user response：

Firstly, counting the value of SalariedFlag isn't going to achieve anything. COUNT counts the number of rows with a non-NULL value, and all your rows have a non-NULL value. Instead you want to COUNT the number of rows where the value of SalariedFlag is 1.

You might, therefore, be able to SUM the column, however, as it's a "flag" it's more likely to be a bit and you can't SUM a bit. Therefore using COUNT and checking the value is 1 with a CASE would likely be better.

Personally, rather than using the restrictive PIVOT operator, I would suggest you use conditional aggregation. This gives you the following:

SELECT YEAR(HireDate) AS HireYear,
       COUNT(CASE WHEN Gender = 'M' AND SalariedFlag = 1 THEN 1 END) AS M,
       COUNT(CASE WHEN Gender = 'F' AND SalariedFlag = 1 THEN 1 END) AS F
FROM #YourTable
GROUP BY YEAR(HireDate);

db<>fiddle