sum if values oracle sql

Question

I have got a query like

 select distinct i.charge_type, cp.name, sum(i.amount)
 from charge.gp_schedule gp, charge.gsm_charge_plan i, charge cp
 where i.code = gp.sales_audit_code
 and cp.code = gp.code
 group by i.charge_type ,cp.name

which outputs for example the following

     GSMFixedCharge         FCFBBR15           15
     **GSMUsageCharge**    Call Charges       2.16
     **GSMUsageCharge**   Service Charges      2
     GSMFixedCharge         Line Rental       23.98
     GSMFixedCharge         FCFAFPBL            1

How can I further sum the values only based on 'GSMUsageCharge' in the same query so the desired output would be

     GSMFixedCharge  FCFBBR15          15
     GSMUsageCharge  Call Charges      4.16
     GSMFixedCharge  Line Rental      23.98
     GSMFixedCharge  FCFAFPBL           1

I have tried something like

  select distinct i.charge_type, cp.name, DECODE(i.charge_type, 'GSMUsageCharge', sum (i.amount)    
   group by i.charge_type) result

but it does not work...

You can't do that without taking out the cp.name because with it you can't group therefore you can't sum. is it really needed? — Jorge Campos
– Jorge Campos, Commented May 4, 2014 at 1:37
Have you considered using a UNION statement using a similar query? Filter any GSM Usage Charges out of the first part of the query and in the second part, filter anything that is not a GSM Usage Charge and sum by cp.name.(no need to group by charge type in the second part of the query as it would only be pulling GSM usage charges). Then just order the whole thing by cp.name :) — Kirk Logan
– Kirk Logan, Commented May 4, 2014 at 1:39

Glenn · Accepted Answer · 2014-05-04 02:25:49Z

1

You could try the following query. First, the i.charge_type and cp.name values are modified for '** GSMUsageCharge **'. Next, the amounts are summed. Please note that you do not need to use DISTINCT when you use GROUP BY.

SELECT charge_type, name, sum(amount)
FROM
( select 
    CASE 
        WHEN i.charge_type = '**GSMUsageCharge**' THEN 'GSMUsageCharge'
        ELSE i.charge_type
    END charge_type,
    CASE 
        WHEN i.charge_type='**GSMUsageCharge**' AND cp.name='Service Charges' THEN 'Call Charges'
        ELSE cp.name
    END name,
    i.amount amount
 from charge.gp_schedule gp, charge.gsm_charge_plan i, charge cp
 where i.code = gp.sales_audit_code
 and cp.code = gp.code
) modified_names
group by charge_type, name;

edited May 4, 2014 at 2:25

Glenn

9,2102 gold badges44 silver badges56 bronze badges

answered May 4, 2014 at 1:59

Joseph B

5,7291 gold badge17 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Glenn Over a year ago

@user3017335 Looks like there was a comma missing after the first case column.

o0x258 Over a year ago

You could avoid the sub select by implementing the "sum if" as follows: SUM(CASE WHEN i.charge_type='**GSMUsageCharge**' THEN 1 ELSE 0 END). I know the question is pretty old but maybe this helps any visitor.

Kirk Logan · Accepted Answer · 2014-05-04 02:28:04Z

0

Just wanted to elaborate a little further on my comment above. Youll have to forgive me if i missed a few things, i threw this together very quickly just to set you in a possible correct direction. Im also used to MYSQL so if I missed any nuances just let me know :)

SELECT * FROM (
     (
     select distinct i.charge_type, cp.name, sum(i.amount)
     from charge.gp_schedule gp, charge.gsm_charge_plan i, charge cp
     where i.code = gp.sales_audit_code
     and i.charge_type <> '**GSMUsageCharge**'
     and cp.code = gp.code
     group by i.charge_type ,cp.name
     ) 
UNION 
     (
     select distinct i.charge_type, cp.name, sum(i.amount)
     from charge.gp_schedule gp, charge.gsm_charge_plan i, charge cp
     where i.code = gp.sales_audit_code
     and i.charge_type = '**GSMUsageCharge**'
     and cp.code = gp.code
     group by cp.name
     ) 
) result
ORDER BY name

edited May 4, 2014 at 2:28

answered May 4, 2014 at 1:45

Kirk Logan

7832 gold badges9 silver badges24 bronze badges

8 Comments

user3017335 Over a year ago

I guess this won't work since the second query after union you need to group by i.charge_type as well

user3017335 Over a year ago

And the thing is that yes I need the cp.name at least for GSMFixedCharge

Kirk Logan Over a year ago

The idea behind the second query is that it would only pull the GSM usage charges. Grouping by charge_type seems redundant to me, as there would only ever be one charge_type returned?

user3017335 Over a year ago

I mean that in terms of syntax you need to group on i.charge_type and cp.name and not just cp.name

Dan Bracuk Over a year ago

Here is a nuance you missed. MySQL is the only database engine that allows you to mismatch the select and group by clauses.

|

Collectives™ on Stack Overflow

sum if values oracle sql

2 Answers 2

2 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related