Home » SQL & PL/SQL » SQL & PL/SQL » Calculating Percent on running total
| Calculating Percent on running total [message #228799] |
Wed, 04 April 2007 04:36  |
martyr
Messages: 2
Registered: April 2007
|
Junior Member |
|
|
I am trying to use percent functions for a process I am writing
The problem I need to solve is:
Have to calculate numbers of males and females in each grade in each department and get the sum in each grade and then in the department as a whole. Then compute the percent of the cumulative total and percentage male and female in each grade
The code i am using is
select position,
pr.title,
p.gender,
decode(p.gender ,'M' ,'M') male,
decode(p.gender ,'F' ,'F') female,
h.name DEPT
from x b, y e, z pr, w tp,
s h, t p
where e.empno = tp.empno
and e.empno = p.personnel_no
and h.department = p.department
and tp.position = pr.posn_code
and b.per_sys_person = e.empno
and tp.position in ('104', '102','111', '106','180', '170', '172')
order by pos
so my output is
Grade Female Male Total
Secretary 10 67 77
Assistant 15 63 78
but it should be
Grade Female Male Total
Secretary 10 13% 67 87% 77
Assistant 15 19% 63 81% 78
Thanks in advance
|
|
|
|
|
|
|
|
| Re: Calculating Percent on running total [message #228809 is a reply to message #228807] |
Wed, 04 April 2007 05:10  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
you cannot use aliases, you need to use the underlying columns/calculations.
Edit: and I can't figure out how you get the output you claim from the select you posted.
MHE
PS: and use the tags [code] and [/code] to preserve formatting of your code snippets. Please do. If you're uncertain about this, read the two topmost topics here ("stickies").
[Updated on: Wed, 04 April 2007 05:14] Report message to a moderator |
|
|
|
|
|
| Re: Calculating Percent on running total [message #228997 is a reply to message #228799] |
Wed, 04 April 2007 23:02 |
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
|
Senior Member |
|
|
| martyr wrote on Wed, 04 April 2007 04:36 | I am trying to use percent functions for a process I am writing
The problem I need to solve is:
Have to calculate numbers of males and females in each grade in each department and get the sum in each grade and then in the department as a whole. Then compute the percent of the cumulative total and percentage male and female in each grade
The code i am using is
select position,
pr.title,
p.gender,
decode(p.gender ,'M' ,'M') male,
decode(p.gender ,'F' ,'F') female,
h.name DEPT
from x b, y e, z pr, w tp,
s h, t p
where e.empno = tp.empno
and e.empno = p.personnel_no
and h.department = p.department
and tp.position = pr.posn_code
and b.per_sys_person = e.empno
and tp.position in ('104', '102','111', '106','180', '170', '172')
order by pos
|
count male percentage like
(count(decode(p.gender ,'M' ,1,0))/count(*))*100 ,
and u get female percentage same ways
|
|
|
|
| Re: Calculating Percent on running total [message #229122 is a reply to message #228997] |
Thu, 05 April 2007 08:22 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| muzahidul islam wrote on Thu, 05 April 2007 00:02 |
count male percentage like
(count(decode(p.gender ,'M' ,1,0))/count(*))*100 ,
and u get female percentage same ways
|
I think you (notice that you has three letter in it, not just one) meant SUM instead of COUNT.
|
|
|
|
Goto Forum:
Current Time: Thu Dec 05 00:09:44 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
