Home » RDBMS Server » Server Administration » Count broken down By month. URGENT!!!!!!!!!!!!!!!!!!!!!!!!!
Count broken down By month. URGENT!!!!!!!!!!!!!!!!!!!!!!!!! [message #373383] |
Fri, 13 April 2001 10:20 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
aish74
Messages: 19 Registered: March 2001
|
Junior Member |
|
|
I want SQL TO COUNT the no of employees worked in the particular division for a particular month. It should be broken down BY division AND month.
something LIKE the following:
Division JAN FEB MAR APR
A 10 9 11 7
B 3 6 12 8
C 4 4 4 4
In the month of Jan the no of working employees In division "A" are 10 and 9 in FEB .
Similarly In the month of Jan the no of working employees In division "B" are 3 and 6 in FEB .
COuld any body help me .
Its URGENT please
|
|
|
|
Re: Count broken down By month. URGENT!!!!!!!!!!!!!!!!!!!!!!!!! [message #373406 is a reply to message #373383] |
Sun, 15 April 2001 10:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Krishnan
Messages: 18 Registered: October 2000
|
Junior Member |
|
|
since columns should represent Month then you need 12 in line views to represent the columns while the division wise count can be done by using group by function hence your query should be some thing like this
I assume that there is master tables that wouls have all the divisions say div_mst and emp_mst having the details of employees
select a.div, ja.cnt, fe.cnt,ma.cnt,ap.cnt,my.cnt,ju.cnt,jl.cnt,au.cnt,se.cnt,oc.cnt,no.cnt,de.cnt
from
div_mst a,
(select div, count(*) cnt from
where to_char(date_fld,'MON') = 'JAN'
emp_mst group by div ) ja,
(select div, count(*) cnt from
where to_char(date_fld,'MON') = 'FEB
emp_mst group by div ) fe,
(select div, count(*) cnt from
where to_char(date_fld,'MON') = 'MAR
emp_mst group by div ) ma
(select div, count(*) cnt from
where to_char(date_fld,'MON') = 'APR
emp_mst group by div ) ap,
(select div, count(*) cnt from
where to_char(date_fld,'MON') = 'MAY'
emp_mst group by div ) my
(select div, count(*) cnt from
where to_char(date_fld,'MON') = 'JUN
emp_mst group by div ) ju,
(select div, count(*) cnt from
where to_char(date_fld,'MON') = 'JUL'
emp_mst group by div ) jl,
(select div, count(*) cnt from
where to_char(date_fld,'MON') = 'AUG'
emp_mst group by div ) AU,
(select div, count(*) cnt from
where to_char(date_fld,'MON') = 'SEP'
emp_mst group by div ) se,
(select div, count(*) cnt from
where to_char(date_fld,'MON') = 'OCT'
emp_mst group by div ) oc,
(select div, count(*) cnt from
where to_char(date_fld,'MON') = 'NOV'
emp_mst group by div ) no,
(select div, count(*) cnt from
where to_char(date_fld,'MON') = 'DEC'
emp_mst group by div ) de,
where a.div = je.div(+)
and a.div = fe.div(+)
and a.div = ma.div(+)
and a.div = ap.div(+)
and a.div = my.div(+)
and a.div = ju.div(+)
and a.div = jl.div(+)
and a.div = au.div(+)
and a.div = se.div(+)
and a.div = oc.div(+)
and a.div = no.div(+)
and a.div = de.div(+)
|
|
|
Goto Forum:
Current Time: Fri Sep 06 15:11:11 CDT 2024
|