CODEWITHSUNDEEP
sqlsql-server
crazyTechie
crazyTechie

Reputation: 2557

SQl server query multiple aggregate columns

I need to write a query in sql server to data get like this.

qry

Essentially it is group by dept, race, gender and then

SUM(employees_of_race_by_gender),Sum(employees_Of_Dept).

I could get data of first four columns, getting sum of employees in that dept is becoming difficult. Could you pls help me in writing the query?

All these details in same table Emp. Columns of Emp are Emp_Number, Race_Name,Gender,Dept

Upvotes: 0

Views: 9057

Answers (2)

gbn
gbn

Reputation: 432361

Your "num_of_emp_in_race" is actually by Gender too

SELECT DISTINCT
    Dept,
    Race_name,
    Gender,
    COUNT(*) OVER (PARTITION BY Dept, Race_name, Gender) AS num_of_emp_in_race,
    COUNT(*) OVER (PARTITION BY Dept) AS num_of_emp_dept
FROM
    MyTable

You should probably have this

    COUNT(*) OVER (PARTITION BY Dept, Gender) AS PerDeptRace
    COUNT(*) OVER (PARTITION BY Dept, Race_name) AS PerDeptGender,
    COUNT(*) OVER (PARTITION BY Dept, Race_name, Gender) AS PerDeptRaceGender,
    COUNT(*) OVER (PARTITION BY Dept) AS PerDept

Edit: the DISTINCT appears to be applied before the COUNT (which would odd based on this) so try this instead

SELECT DISTINCT
    *
FROM
    (
    SELECT 
        Dept,
        Race_name,
        Gender,
        COUNT(*) OVER (PARTITION BY Dept, Race_name, Gender) AS num_of_emp_in_race,
        COUNT(*) OVER (PARTITION BY Dept) AS num_of_emp_dept
    FROM
        MyTable
    ) foo

Upvotes: 7

David Schmitt
David Schmitt

Reputation: 59356

Since the two sums you're looking for are based on a different aggregation, you need to calculate them separately and join the result. In such cases I first build the selects to show me the different results, making it easy to catch errors early:

SELECT Dept, Gender, race_name, COUNT(*) as num_of_emp_in_race
FROM Emp
GROUP BY 1, 2, 3

SELECT Dept, COUNT(*) as num_of_emp_in_dept
FROM Emp
GROUP BY 1

Afterwards, joining those two is pretty straight forward:

SELECT * 
FROM ( first statement here ) as by_race
JOIN ( second statement here ) as by_dept ON (by_race.Dept = by_dept.Dept)

Upvotes: 1

Related Questions