CODEWITHSUNDEEP
mysqlsql
peku33
peku33

Reputation: 3903

Using grouped and non-grouped column in the same time

I have a table with payment requests.

PaymentId (UNSIGNED INT AI)
ClientId (USINGNED INT)
Amount (FLOAT)
PayTo (DATE)

Because for PayTo there are usually not more than 2-3 distinct values (per client) I want to create the list - how much money should be paid up to this day. Do I was looking for a query like this:

SELECT PayTo, SUM(IF(PayTo <= PayTo, Amount, 0)) as AmountThisDate 
FROM Payments 
WHERE ClientId='%d' 
GROUP BY PayTo
ORDER BY PayTo DESC

But I'm 100% sure

PayTo <= PayTo

won't work.

What is the correct way to access GROUP BY Column and TABLE column at once if it is the same column?

Upvotes: 0

Views: 50

Answers (2)

Akhil
Akhil

Reputation: 2602

SELECT PayDate, SUM(IF(PayDate <= current_date(), Amount, 0)) as AmountThisDate 
FROM Payments 
WHERE ClientId='%d' 
GROUP BY PayDate 
ORDER BY PayDate DESC

Is this what you were looking for?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270191

It sounds like you want a cumulative sum. Here is a method using a correlated subquery:

SELECT p1.PayDate,
       (select sum(p2.amount)
        from Payments p2
        where p2.ClientId = p.ClientId and
              p2.PayDate <= p.PayDate
       ) as AmountThisDate
FROM Payments p1
WHERE p1.ClientId='%d' 
GROUP BY p1.PayDate 
ORDER BY p1.PayDate DESC;

Performance will be improved by having an index on payments(ClientId, PayDate, Amount).

Upvotes: 1

Related Questions