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 Go to next message
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 #228805 is a reply to message #228799] Wed, 04 April 2007 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
martyr
Messages: 2
Registered: 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 Go to previous messageGo to next message
Maaher
Messages: 7062
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 #228815 is a reply to message #228807] Wed, 04 April 2007 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
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 Go to previous message
joy_division
Messages: 4640
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.
Previous Topic: Help me build this string
Next Topic: droping all tables in a schema
Goto Forum:
  


Current Time: Mon Dec 05 11:13:08 CST 2016

Total time taken to generate the page: 0.13442 seconds