Count SQL Statement Without Create Other View

Question

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.

Clodoaldo Neto · Accepted Answer · 2012-10-12 11:12:40Z

1

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

edited Oct 12, 2012 at 11:12

answered Oct 12, 2012 at 1:09

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

goh6319 Over a year ago

hi, your suggestion is good.as my feedback but it didn't work in oracle SQL.Anyway thank you very much.

Clodoaldo Neto Over a year ago

@goh6319 Then it is Oracle, not MySQL and not SQL Server as in the tags?

Clodoaldo Neto Over a year ago

@goh6319 Added Oracle version

goh6319 Over a year ago

i just need the concept of do it. But u already provided me with a good solution.Thank you very much.

Z . · Accepted Answer · 2012-10-12 01:06:33Z

0

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

answered Oct 12, 2012 at 1:06

Z .

12.8k2 gold badges34 silver badges59 bronze badges

2 Comments

goh6319 Over a year ago

Hi Zgravko, thank you for your suggestion. But your solution only able to display the total. Let said i wish to display each number of male and female. How do you perform it? (^^) without create a new view.

Z . Over a year ago

SUM(CASE WHEN Department = 'IT' AND Gender = 'M' THEN 1 ELSE 0) END AS IT_M, <-- this will get you the count of Males in IT, you need one of those for each department/gender...

Collectives™ on Stack Overflow

Count SQL Statement Without Create Other View

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related