Home » SQL & PL/SQL » SQL & PL/SQL » Calculating Percent on running total
Calculating Percent on running total Wed, 04 April 2007 04:36
 martyr Messages: 2Registered: 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 #228805 is a reply to message #228799] Wed, 04 April 2007 04:54
 Michel Cadot Messages: 64831Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
If you already have "Female", "Male" and "Total", percentages are just 100*"Female"/"Total" and 100*"Male"/"Total".

(Btw, I don't know how you can get the first output with your query.)

Regards
Michel
Re: Calculating Percent on running total [message #228807 is a reply to message #228805] Wed, 04 April 2007 05:00
 martyr Messages: 2Registered: April 2007 Junior Member
Thanks for your reply

but when i use 'Female' or 'Male' then i get the error message
'Invalid Identifier'

i am using oracle reports for the output so that how i got my output

thanks
Re: Calculating Percent on running total [message #228809 is a reply to message #228807] Wed, 04 April 2007 05:10
 Maaher Messages: 7062Registered: 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 #228815 is a reply to message #228807] Wed, 04 April 2007 05:35
 Michel Cadot Messages: 64831Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
As Maarten said, you have to use the underlying calculation or columns, I use "Male"... as shortcuts as we don't have your real query.

Regards
Michel
Re: Calculating Percent on running total [message #228997 is a reply to message #228799] Wed, 04 April 2007 23:02
 muzahid Messages: 281Registered: 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: 4747Registered: 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.
 Previous Topic: Help me build this string Next Topic: droping all tables in a schema
Goto Forum:

Current Time: Fri May 26 02:25:25 CDT 2017

Total time taken to generate the page: 0.08997 seconds