SQl server query multiple aggregate columns

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

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

Answers


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

Need Your Help

RadioButton on DefaulTableModel

java swing jtable jradiobutton

How can I add a JRadioButton on my table with the help of DefaultTableModel?

Jquery popup under the current text

javascript jquery

I am working on developing a editor which help users to improve their writing.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.