Skip to content
Advertisement

How to count multiple columns in SQL (Oracle) with criteria?

I’m working on SMS-Gateway that holds multiple charged SMS-services with different numbers,
each SMS sent to the customer has 4 status as below (forwarded, delivered, expired,delivery failed)

enter image description here

Now I have the below first_table for the charging-system with the below details (TABLE-A)

enter image description here

and below (TABLE-B) which contain the status of each sent SMS with its ID enter image description here

Below is my expected final result to forecast the details for each sms-service :

enter image description here

At first I thought it was easy all I need is just to use COUNT(Case when ...)
but in my case I have thousands of SMS-numbers(services) so if I use this approach it will be like that:-

COUNT(CASE WHEN a.SMS_SHORT_CODE='1111' AND B.STATUS='forwarded' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='1111' AND B.STATUS='delivered' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='1111' AND B.STATUS='expired' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='1111' AND B.STATUS='delivery failed' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='5000' AND B.STATUS='forwarded' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='5000' AND B.STATUS='delivered' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='5000' AND B.STATUS='expired' )
COUNT(CASE WHEN a.SMS_SHORT_CODE='5000' AND B.STATUS='delivery failed' )
...
...
...
...
...
...
...

The above approach not practical when you have many services also noting that CASE can handle only 250 conditions?

So what is the best approach to do left outer join for (Table A) on (Table B) using the SMS-ID and count each SMS-status and forecast it as below?

enter image description here

Advertisement

Answer

I would suggest conditional aggregation:

select b.SMS_SHORT_CODE,
       sum(case when status = 'forwaded' then 1 else 0 end) as count_of_forwaded,
       sum(case when status = 'delivered' then 1 else 0 end) as count_of_status,
       sum(case when status = 'expired' then 1 else 0 end) as count_of_expired,
       sum(case when status = 'delivery failed' then 1 else 0 end) as count_of_delivery_failed
from TABLEB b
group by b.SMS_SHORT_CODE ;

Note that no JOIN is necessary. All the data you want to aggregate is in TABLEB.

4 People found this is helpful
Advertisement