How to write Oracle Query with multiple statistics

Question

How to get data using a single query with multiple aggregation calculation points.

I have data like this.

DEPT EMPLOYEE SALARY ACTIVE
---- --------------- ----------- ---------

D1        E1                 1,000           Y
D1        E2                 2,000           N
D1        E3                 3,000           Y
D1        E4                 4,000           Y
D2        E6                 5,000           Y
D2        E7                 1,000           N
D2        E8                 3,000           N
D3        E9                 4,000           Y

I want output something like this, in a single query.

DEPT   TOT_ACTIVE_EMP   TOT_INACTIVE_EMP   TOT_ACTIVE_SAL   TOT_INACTIVE_SAL
-----      -------------------------   --------------------------    ------------------------   --------------------------
D1            3                                1                                  8,000                         2,000
D2            1                                2                                  5,000                         4,000
D3            1                                0                                  4,000                           0

score 2 · Answer 1 · answered Jun 01 '14 at 03:38

You can do something like

SELECT dept,
       SUM( CASE WHEN active='Y' THEN 1 ELSE 0 END) tot_active_emp,
       SUM( CASE WHEN active='N' THEN 1 ELSE 0 END) tot_inactive_emp,
       SUM( CASE WHEN active='Y' THEN salary ELSE 0 END) tot_active_sal,
       SUM( CASE WHEN active='N' THEN salary ELSE 0 END) tot_inactive_sal
  FROM you_table
 GROUP BY dept

score 0 · Answer 2 · answered Jun 01 '14 at 03:39

You can do this using case statements

SELECT 
dept, 
sum(case when active = 'Y' then 1 else 0 end) tot_active_emp,
sum(case when active = 'Y' then salary else 0 end) tot_active_sal
FROM emp_table
GROUP BY dept;

with similar case statement for the other fields you need.

score 0 · Answer 3 · answered Jun 01 '14 at 03:39

You can use a series of CASE statements and SQL server COUNT/SUM function, i.e.

SELECT
  dept,
  sum(CASE WHEN ACTIVE='Y' THEN 1 ELSE 0 END) AS total_active,
  sum(CASE WHEN ACTIVE='Y' THEN 0 ELSE 1 END) AS total_inactive
GROUP BY
  dept

Exercise for the reader to complete for the other columns :)

How to write Oracle Query with multiple statistics

3 Answers3