Calculating in an SQL

[lhg1]

Hi

I have this SQL in ORACLE

SELECT 
SUM(num_of_calls) AS SMS_CALLS,
SUM(NVL(actv_amt,0))+SUM(NVL(-discount_amt,0)) AS total_charge, 
SUM(NVL(discount_amt,0)) AS DISCOUNT,
SUM(NVL(actv_amt,0)) AS BILLED,
FROM charge c1 
WHERE actv_bill_seq_no = (SELECT ba.bl_last_rg_bl_seq_no FROM billing_account ba WHERE c1.ban = ba.ban)
AND feature_category IN ('GGSM', 'NBR1', 'NBR3', 'NBR4', 'NBR5', 'NBR6', 'NBZO',
'NDAT', 'NEUR', 'NF2M','NFAX','NFLG','NFP0','NFSM','NGP1','NHSD','NHZO','NIAC','NMDA','NMPG','NOZO','NSG0','NSO6','NST1','NSTA','NUDP')
AND ROOT_BAN=99999999;

This returns 4 rows, what I want is to calculate the how many pct the discount is.

Some thing like this

SELECT 
SUM(num_of_calls) AS SMS_CALLS,
SUM(NVL(actv_amt,0))+SUM(NVL(-discount_amt,0)) AS total_charge, 
SUM(NVL(discount_amt,0)) AS DISCOUNT,
SUM(NVL(actv_amt,0)) AS BILLED,
[b](DISCOUNT*-1)/total_charge AS DISC_PCT[/b]
FROM charge c1 
WHERE actv_bill_seq_no = (SELECT ba.bl_last_rg_bl_seq_no FROM billing_account ba WHERE c1.ban = ba.ban)
AND feature_category IN ('GGSM', 'NBR1', 'NBR3', 'NBR4', 'NBR5', 'NBR6', 'NBZO',
'NDAT', 'NEUR', 'NF2M','NFAX','NFLG','NFP0','NFSM','NGP1','NHSD','NHZO','NIAC','NMDA','NMPG','NOZO','NSG0','NSO6','NST1','NSTA','NUDP')
AND ROOT_BAN=326626300;

But I can't get it to work.

Hope someone can help

/LHG

 

[PHV]

(SUM(NVL(discount_amt,0))*-1)/SUM(NVL(discount_amt,0)) AS DISC_PCT


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

does oracle not follow ANSI SQL and ignore NULLs in aggregate functions like SUM?

what is the difference between SUM(NVL(col,0)) and SUM(col)?

r937.com | rudy.ca