Farhana - 1 year ago 23

SQL Question

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.

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;
```