CODEWITHSUNDEEP
sqlpivot
NoChance
NoChance

Reputation: 5772

SQL Aggregation prior to advanced SQL

Given a simple case like this:

Gender DeptID
--------------
M        1
F        1
M        2
F        2 
F        2

What SQL statement I need to write If I want to generate the following result using SQL without using advanced CUBE, Rollup, etc., just using plain SQL-92:

GenderSum Dept1Sum Dept2Sum
----------------------------
M           1         1
F           1         2

I was wondering how such information would be generated by ETL in the past using SQL?

Remark: It is possible to use Group by on gender and union that with a group by on DetptId to get a vertical result set but this is clearly not what I want.

Upvotes: 1

Views: 37

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522211

You can try the following pivot query:

SELECT Gender AS GenderSum,
       SUM(CASE WHEN DeptID = 1 THEN 1 ELSE 0 END) AS Dept1Sum,
       SUM(CASE WHEN DeptID = 2 THEN 1 ELSE 0 END) AS Dept2Sum
FROM yourTable
GROUP BY Gender

Upvotes: 3

Related Questions