Home » SQL & PL/SQL » SQL & PL/SQL » Sum from part of coloumn (Oracle,11g Release 11.2.0.4.0, Solaris 11)
Sum from part of coloumn [message #654543] Fri, 05 August 2016 06:28 Go to next message
nms123
Messages: 2
Registered: August 2016
Junior Member
Hi all,

I have a query in which it returns the following output:

select c.name Product_Name,count(a.cli) Subscriber from ccs_acct_reference a,ccs_acct_acct_references b,ccs_acct_type c, be_wallet d where a.id=b.acct_reference and b.account_type=c.id and b.account=d.id and d.state='A' group by c.name

PRODUCT_NAME		SUBSCRIBER
----------------------------------
201502-b31s31i10 	87
201502-b31s31i10R60-60	2
201511-b31s31i15	3094
Simpel PostPay		85
Simpel PrePay		14536
Simpel PrePay Test	1


I am trying to alter this query by having the output that adds the product_name, 201502-b31s31i10, 201502-b31s31i10R60-60, 201511-b31s31i15 and Simpel PostPay. Then adds the Simpel PrePay and Simpel PrePay Test seperatly. So basically the idea is to have this output:

PRODUCT_NAME		SUBSCRIBER
----------------------------------
First_sum 	        3268
Second_sum		14537


Could you kindly help me out to achieve this?

Many thanks
Re: Sum from part of coloumn [message #654551 is a reply to message #654543] Fri, 05 August 2016 08:38 Go to previous messageGo to next message
flyboy
Messages: 1899
Registered: November 2006
Senior Member
Hi,

something like this could achieve it:
select case when c.name in ( '201502-b31s31i10', '201502-b31s31i10R60-60'
                           , '201511-b31s31i15', 'Simpel PostPay' )
                  then 'First_sum'
                  else 'Second_sum'
             end Product_Name, count(a.cli) Subscriber
from ccs_acct_reference a,ccs_acct_acct_references b,ccs_acct_type c, be_wallet d
where a.id=b.acct_reference
  and b.account_type=c.id
  and b.account=d.id
  and d.state='A'
group by case when c.name in ( '201502-b31s31i10', '201502-b31s31i10R60-60'
                             , '201511-b31s31i15', 'Simpel PostPay' )
              then 'First_sum'
              else 'Second_sum'
         end
;
You may adjust the condition(s) inside the CASE expression according to the exact required rules (as the logic in your post is not very clear).
Re: Sum from part of coloumn [message #654556 is a reply to message #654551] Fri, 05 August 2016 09:47 Go to previous message
nms123
Messages: 2
Registered: August 2016
Junior Member
Hi flyboy,

it worked perfectly. The case expression is what exactly what i was after.

Thank you for your input

[Updated on: Fri, 05 August 2016 09:47]

Report message to a moderator

Previous Topic: Help using regexp_substr - Urgent Help needed
Next Topic: UTL_DBWS error: ORA-29540: class oracle/jpub/runtime/dbws/DbwsProxy does not exist
Goto Forum:
  


Current Time: Sun Jun 13 15:37:11 CDT 2021