Sum from part of coloumn [message #654543] |
Fri, 05 August 2016 06:28 |
|
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 |
flyboy
Messages: 1903 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).
|
|
|
|