CODEWITHSUNDEEP
mysql
XAF
XAF

Reputation: 1472

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.

Upvotes: 1

Views: 43

Answers (2)

Pavel Zimogorov
Pavel Zimogorov

Reputation: 1442

Try this one: 

select sum(paidWithDelay), 
       sum(Overdue), 
       sum(paidOnTime), 
       sum(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
) t1;

Upvotes: 1

Priyanshu
Priyanshu

Reputation: 881

    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;

Upvotes: 2

Related Questions