Farhana Farhana - 7 months ago 12
SQL Question

return 1 row instead of multiple rows

Hi I have a query which is currently returning me 4 rows.

SELECT (case when a.duedate < '2016-04-26' and a.total_paid > 0
then count(a.duedate)
else 0 end) as paidWithDelay,
(case when a.duedate < '2016-04-26' and a.total_paid = 0
then count(a.duedate)
else 0 end) as Overdue,
(case when a.duedate > '2016-04-26' and a.total_paid > 0
then count(a.duedate)
else 0 end) as paidOnTime,
(case when a.duedate > '2016-04-26' and a.total_paid = 0
then count(a.duedate)
else 0 end) as waitingForPayment
FROM payment_plan a
where a.payor_orig_id = 611 and a.UPDATE_DT is null
group by a.duedate;


Like this in the picture.enter image description here
Basically what I want is to return just one row
Like this
paidWithDelay 2 , overdue 1 , paidontime 0 and waitingForPayment 1 ,

I tried writing sum front of case , but did not work.

Answer
    select sum(paidwithdelay)paidwithydelay,sum(overdue)overdue,sum(paidontime)paidontime,sum(waitingforpayment)waitingforpayment 
from (SELECT (case when a.duedate < '2016-04-26' and a.total_paid > 0 
            then  count(a.duedate)
            else 0 end) as paidWithDelay,
            (case when a.duedate < '2016-04-26' and a.total_paid = 0 
            then  count(a.duedate)
            else 0 end) as Overdue,
            (case when a.duedate > '2016-04-26' and a.total_paid > 0  
            then count(a.duedate)
            else 0 end) as paidOnTime,
            (case when a.duedate > '2016-04-26' and a.total_paid = 0  
            then  count(a.duedate)
            else 0 end) as waitingForPayment
            FROM payment_plan a
            where a.payor_orig_id = 611 and a.UPDATE_DT is null
            group by a.duedate)temp;