SQL: Trying to sum multiple entries to an account by payment date

Question

I'm using SQL, trying to write a query that will sum payment amounts made on the same day to an account. I'm not having luck and hoping someone could shine some light on which approach to take. Basically, if an account posts two payments in the same day, I want the information on a pivot table that breaks down the Account, Payment Date, Sum of Payments.

Original Data File

Account Payment Date    Amount
10001   6/20/2014   10
10002   6/20/2014   10
10003   6/20/2014   10
10004   6/20/2014   10
10001   6/20/2014   10
10002   6/19/2014   10
10003   6/19/2014   10
10004   6/18/2014   10
10002   6/19/2014   10

Want to create this table:

Row Labels  Date    Sum of Amount
10001   6/20/2014   20
10002   6/19/2014   20
10002   6/20/2014   10
10003   6/19/2014   10
10003   6/20/2014   10
10004   6/18/2014   10
10004   6/20/2014   10

Please show us what you have tried so far. – Dave C Jun 20 '14 at 19:22 — Dave C, Jun 20 '14 at 19:22

score 0 · Answer 1 · edited May 23 '17 at 10:25

The following query will give you the output you said you needed...

SELECT
  [Account],
  [Payment Date],
  SUM([Amount]) AS "Sum of Payments"
FROM [transactions]
GROUP BY
  [Account],
  [Payment Date]
ORDER BY
  [Account] ASC,
  [Payment Date] ASC
;

(You can play with this on this SQL Fiddle session)

That said, it's so simple a case of GROUP BY usage I'm wondering whether you wanted a running total there instead.

SQL: Trying to sum multiple entries to an account by payment date

1 Answers1