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
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

Report message to a moderator

Re: Count broken down By month. URGENT!!!!!!!!!!!!!!!!!!!!!!!!! [message #373393 is a reply to message #373383] Fri, 13 April 2001 12:17 Go to previous messageGo to next message
Latha
Messages: 9
Registered: September 2000
Junior Member
select division, workedmon, count(*) "no. employees" from divmon group by division, workedmon;

"you have to format it in reports matrix form"
"if any thing is wrong i am sorry, so please correct me since i am a student. thank you".

Report message to a moderator

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
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(+)

Report message to a moderator

Previous Topic: Re: dynamic SQL and DBMS_SQL
Next Topic: help with sql for college student
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Jul 02 02:39:36 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.