GROUP BY only rows with a certain condition and date
I have a basic table in my database that has a simple transactions history
such as depositing money to an account, withdrawing money, and having
money refunded. Each entry has a date associated with it.
I have a php page that displays this transactional information row by row
and it looks great. However, is there a way within the mySQL query that I
can "collapse" all "refunded" money transactions into groupings by day?
It's quite easy to do a GROUP_BY on the transaction type but I only want
to group by on if the transaction_type is of type "refund" and those
refunds happened on the same day. Thanks!
My Current Example Table:
Jan 3, Deposit, $100
Jan 3, Deposit, $200
Jan 2, Withdraw, $50
Jan 2, Refund, $100
Jan 2, Refund, $100
Jan 2, Deposit, $200
Jan 2, Refund, $100
Jan 1, Deposit, $100
How I would like the data to display
Jan 3, Deposit, $100
Jan 3, Deposit, $200
Jan 2, Withdraw, $50
Jan 2, Refund, $300 < this row has collapsed all Refunds for Jan 2nd into
1 row
Jan 2, Deposit, $200
Jan 1, Deposit, $100
The current SQL is something like:
SELECT transaction_date, transaction_type, transaction_amount
FROM transaction_history
WHERE customer_id = $customer_id
ORDER BY transaction_date DESC
No comments:
Post a Comment