0

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

justinl
  • 10,448
  • 21
  • 70
  • 88

1 Answers1

2

give this a try,

SELECT  transaction_date, transaction_type, SUM(transaction_amount)
FROM    TableName
-- WHERE customer_id = $customer_id
GROUP   BY transaction_date, 
           CASE WHEN transaction_type = 'refund' THEN 1 ELSE RAND() END
ORDER   BY transaction_date DESC
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 1
    Better to use `id` (if such a column exists) than `rand()`, yes? Also, you need to group by date as well – Blorgbeard Sep 18 '13 at 22:30
  • @Blorgbeard yep if `id` is unique then i agree `:)` oh, forgot to add the date in the group by. – John Woo Sep 18 '13 at 22:39
  • I tweaked your fiddle to handle refunds on different days: http://sqlfiddle.com/#!2/a262e/1 – Blorgbeard Sep 18 '13 at 22:39
  • Thanks! I'm just going to give it a try now. By the way each transaction does have a unique ID which is auto incremented so I supposed I could also ORDER BY the id. – justinl Sep 18 '13 at 22:59
  • Guys like you make the internet awesome. Thanks for helping me! – justinl Sep 18 '13 at 23:12