Home » SQL & PL/SQL » SQL & PL/SQL » Function Help
Function Help [message #250053] Sat, 07 July 2007 08:10 Go to next message
patneel
Messages: 44
Registered: June 2007
Location: India
Member
Hi All,

i am giving brief requiremnt for my problem

Below is col name and there values.

Program Name Sold Unit

SF1 6

SF2 10

SF3 30

Here i want to happen following calucation

First it do individual calculation then Sum it up an then divide

Individual Calculation

6-mod(6,15)=0
10-mod(10,15)=0
30-mod(30,15)=30

(0+0+30)/30=1

I have written formula like this

Sum((C600_SOLDUNITS-MOD(C600_SOLDUNITS,15)))/30

But instead of 1 giving me 4

i am doubtful about my postion of sum funtction can anyone give some suggestion here?

Thanks
Re: Function Help [message #250055 is a reply to message #250053] Sat, 07 July 2007 08:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Leave out the sum first, to see the individual results. Make sure they are correct and then work your way towards the end-result.
Re: Function Help [message #250057 is a reply to message #250053] Sat, 07 July 2007 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>i am doubtful about my postion of sum funtction
I am doubtful of assertions in above/this post.
Please use SQL*Plus
SQL> SET TERM ON ECHO ON
SQL> --show all steps to prior & including SUM function using CUT & PASTE to show use the whole session

In other words, do NOT describe what you think happened.
Use CUT & PASTE to show us exactly what you did & how Oracle responded
Re: Function Help [message #250063 is a reply to message #250053] Sat, 07 July 2007 09:29 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    data as (
  3      select 6 C600_SOLDUNITS from dual union all select 10 from dual union select 30 from dual
  4    )
  5  select Sum((C600_SOLDUNITS-MOD(C600_SOLDUNITS,15)))/30
  6  from data
  7  /
SUM((C600_SOLDUNITS-MOD(C600_SOLDUNITS,15)))/30
-----------------------------------------------
                                              1

1 row selected.

Something wrong with you.

Regards
Michel
Previous Topic: Display first value only for repeated values in a column
Next Topic: How to check does record exsist?
Goto Forum:
  


Current Time: Sun Dec 11 06:10:01 CST 2016

Total time taken to generate the page: 0.08522 seconds