CODEWITHSUNDEEP
mysqlsqloracle-database
goh6319
goh6319

Reputation: 137

Count SQL Statement Without Create Other View

I have a table name as JOB_Details and the data show as below:

 Employee_ID   Age          Department     Gender       
    001       30yrs          IT               M 
    002       34yrs          HR               F    
    003       39yrs          HR               F   
    004       49yrs          Finance          M        
    005       54yrs          IT               M 
    006       20yrs          HR               M 
    007       24yrs          HR               F   
    008       33yrs          Finance          F       
    009       29yrs          Finance          F        
    010       44yrs          IT               M

The output i wish to display should be like

Age          Department:IT       Department:Finance  Department:HR    Total
             Male     Female     Male     Female     Male     Female
<30yrs         1         0         0         1         1         1       4
 30-34yrs      0         0         0         1         0         1       2
 35-39yrs      0         0         0         0         0         1       1
 40-49yrs      1         0         1         0         0         0       2
 50-54yrs      1         0         0         0         0         0       1
 Total         3         0         1         2         1         3       10

Based on my knowledge, the only way can be done it is by create another view and group them one by one. But i wish to know is that other way to do it without create a other view? I wish to learn it if anyone have other good suggestion. Thank you very much.

Upvotes: 1

Views: 238

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125424

MySQL version. Will not work in SQL Server neither Oracle. SQL Fiddle

select *, IT_Male + IT_Female + Finance_Male + Finance_Female + HR_Male + HR_Female as Total
from (
    select 
        ar.`range` as Age,
        count(Department = 'IT' and Gender = 'M' or null) as IT_Male,
        count(Department = 'IT' and Gender = 'F' or null) as IT_Female,
        count(Department = 'Finance' and Gender = 'M' or null) as Finance_Male,
        count(Department = 'Finance' and Gender = 'F' or null) as Finance_Female,
        count(Department = 'HR' and Gender = 'M' or null) as HR_Male,
        count(Department = 'HR' and Gender = 'F' or null) as HR_Female
    from 
        JOB_Details jd
        inner join
        age_range ar on jd.Age between ar.bottom and ar.top
    group by ar.`range`
    order by ar.bottom
) s
union
select 
    'Total',
    count(Department = 'IT' and Gender = 'M' or null),
    count(Department = 'IT' and Gender = 'F' or null),
    count(Department = 'Finance' and Gender = 'M' or null),
    count(Department = 'Finance' and Gender = 'F' or null),
    count(Department = 'HR' and Gender = 'M' or null),
    count(Department = 'HR' and Gender = 'F' or null),
    count(*)
from JOB_Details

Oracle version without external table or view: SQL Fiddle

with age_range as (
    select 0 as "bottom", 29 as "top", '<30' as "range" from dual union
    select 30, 34, '30-34' from dual union
    select 35, 39, '35-59' from dual union
    select 40, 49, '40-49' from dual union
    select 50, 54, '50-54' from dual
)
select s.*, IT_Male + IT_Female + Finance_Male + Finance_Female + HR_Male + HR_Female as Total
from (
    select 
        ar."range" as Age,
        count(case when Department = 'IT' and Gender = 'M' then 1 end) as IT_Male,
        count(case when Department = 'IT' and Gender = 'F' then 1 end) as IT_Female,
        count(case when Department = 'Finance' and Gender = 'M' then 1 end) as Finance_Male,
        count(case when Department = 'Finance' and Gender = 'F' then 1 end) as Finance_Female,
        count(case when Department = 'HR' and Gender = 'M' then 1 end) as HR_Male,
        count(case when Department = 'HR' and Gender = 'F' then 1 end) as HR_Female
    from 
        JOB_Details jd
        inner join
        age_range ar on jd.Age between ar."bottom" and ar."top"
    group by ar."range", ar."bottom"
    order by ar."bottom"
) s
union
select 
    'Total',
    count(case when Department = 'IT' and Gender = 'M' then 1 end),
    count(case when Department = 'IT' and Gender = 'F' then 1 end),
    count(case when Department = 'Finance' and Gender = 'M' then 1 end),
    count(case when Department = 'Finance' and Gender = 'F' then 1 end),
    count(case when Department = 'HR' and Gender = 'M' then 1 end),
    count(case when Department = 'HR' and Gender = 'F' then 1 end),
    count(*)
from JOB_Details

Upvotes: 1

Z .
Z .

Reputation: 12837

SELECT 
   CASE WHEN Age < 30 THEN '<30' 
        WHEN 30 <= Age AND Age < 34 THEN '30-34'
        ...
   END as Age,

   SUM(CASE WHEN Department = 'IT' AND Gender = 'M' THEN 1 ELSE 0) END AS IT_M,
   SUM(CASE WHEN Department = 'IT' AND Gender = 'F' THEN 1 ELSE 0) END AS IT_F,
   ...

FROM JOB_DETAILS

GROUP BY
   CASE WHEN Age < 30 THEN '<30y' 
        WHEN 30 <= Age AND Age < 34 THEN '30-34'
        ...
   END

Upvotes: 0

Related Questions