Tarang Panchal Tarang Panchal - 24 days ago 4
MySQL Question

Combine CASE querie to another JOIN query

Hello I have following query:

SELECT x.id , x.amount , x.amount as paid_amount , SUM(y.bal) as total, x.reciept_no
FROM (SELECT *, paid bal
FROM challan_1 ) x
JOIN (SELECT *, amount bal
FROM challan_1 ) y
ON y.id <= x.id
GROUP BY x.id
HAVING total <= '500'


it's working quite fine. And output like

enter image description here

And then I made a new query that is as below

SELECT *, (CASE WHEN 500-sum(amount) >= 0
THEN '0'
ELSE 500-SUM(paid) END) as pending_amt
FROM challan_1


Output is
enter image description here

This query returns me a Pending Amount so I need to combine both queries so how can I combine both queries.
I need this pending amount in first query.
This is My SQL Fiddle

And I need Like This. Where User Have 500 Currency And Have 3 Payment So for that situation Output Should be like this.
enter image description here
Where 100 Is in pending amount and 200 from user value is debited.

Answer Source

I don't completely understand, but here is my take on this. 500 available. There are records where payments are made. Strange enough even beyond 500, so I assume these are would-be expenses/payments if there were more money available. I stop where payments exceed the 500.

SELECT 
  challan.*, 
  SUM(addup.amount) as total_amount, 
  sum(addup.paid) as total_paid,
  sum(addup.amount) - sum(addup.paid) as total_pending,
  sum(addup.amount) <= sum(addup.paid) as status
FROM challan_1 challan
JOIN challan_1 addup ON addup.id <= challan.id 
GROUP BY challan.id
HAVING sum(addup.paid) <= 500
ORDER BY challan.id;

If you want to show further records, i.e. get rid of the HAVING clause, you'll need another formula for the pending amount, for the highest paid amount possible is 500:

SELECT 
  challan.*, 
  SUM(addup.amount) as total_amount, 
  sum(addup.paid) as total_paid,
  sum(addup.amount) - least(500, sum(addup.paid)) as total_pending,
  sum(addup.amount) <= least(500, sum(addup.paid)) as status
FROM challan_1 challan
JOIN challan_1 addup ON addup.id <= challan.id 
GROUP BY challan.id
ORDER BY challan.id;