Sum a subquery and group by customer info

Question

I have three tables something like the following:

Customer (CustomerID, AddressState)
Account (AccountID, CustomerID, OpenedDate)
Payment (AccountID, Amount)

The Payment table can contain multiple payments for an Account and a Customer can have multiple accounts.

What I would like to do is retrieve the total amount of all payments on a State by State and Month by Month basis. E.g.

Opened Date| State | Total
--------------------------
2009-01-01 |   CA  | 2,500
2009-01-01 |   GA  | 1,000
2009-01-01 |   NY  |   500
2009-02-01 |   CA  | 1,500
2009-02-01 |   NY  | 2,000

In other words, I'm trying to find out what States paid the most for each month. I'm only interested in the month of the OpenedDate but I get it as a date for processing afterwards. I was trying to retrieve all the data I needed in a single query.

I've been trying something along the lines of:

select
  dateadd (month, datediff(month, 0, a.OpenedDate), 0) as 'Date',
  c.AddressState as 'State',
  (
    select sum(x.Amount)
    from (
      select p.Amount
      from Payment p
      where p.AccountID = a.AccountID
    ) as x
  )
  from Account a
  inner join Customer c on c.CustomerID = a.CustomerID
  where ***
  group by
    dateadd(month, datediff(month, 0, a.OpenedDate), 0),
    c.AddressState

The where clause includes some general stuff on the Account table. The query won't work because the a.AccountID is not included in the aggregate function.

Am I approaching this the right way? How can I retrieve the data I require in order to calculate which States' customers pay the most?

Answer 1

In other words, I'm trying to find out what States paid the most for each month

This one will select the most profitable state for each month:

SELECT  *
FROM    (
        SELECT  yr, mon, AddressState, amt, ROW_NUMBER() OVER (PARTITION BY yr, mon, addressstate ORDER BY amt DESC) AS rn
        FROM    (
                SELECT  YEAR(OpenedDate) AS yr, MONTH(OpenedDate) AS mon, AddressState, SUM(Amount) AS amt
                FROM    Customer c
                JOIN    Account a
                ON      a.CustomerID = c.CustomerID
                JOIN    Payments p
                ON      p.AccountID = a.AccountID
                GROUP BY
                        YEAR(OpenedDate), MONTH(OpenedDate), AddressState
                )
        ) q
WHERE   rn = 1

Replace the last condition with ORDER BY yr, mon, amt DESC to get the list of all states like in your resultset:

SELECT  *
FROM    (
        SELECT  yr, mon, AddressState, amt, ROW_NUMBER() OVER (PARTITION BY yr, mon, addressstate ORDER BY amt DESC) AS rn
        FROM    (
                SELECT  YEAR(OpenedDate) AS yr, MONTH(OpenedDate) AS mon, AddressState, SUM(Amount) AS amt
                FROM    Customer c
                JOIN    Account a
                ON      a.CustomerID = c.CustomerID
                JOIN    Payments p
                ON      p.AccountID = a.AccountID
                GROUP BY
                        YEAR(OpenedDate), MONTH(OpenedDate), AddressState
                )
        ) q
ORDER BY
        yr, mon, amt DESC

Answer 2

If you want the data grouped by month, you need to group by month:

SELECT  AddressState, DATEPART(mm, OpenedDate), SUM(Amount)
FROM    Customer c
INNER JOIN Account a  ON a.CustomerID = c.CustomerID
INNER JOIN Payments p ON p.AccountID = a.AccountID
GROUP BY   AddressState, DATEPART(mm, OpenedDate)

This shows you the monthnumber (1-12) and the total amount per state. Note that this example doesn't include years: all amounts of month 1 are summed regardless of year. Add a datepart(yy, OpenedDate) if you like.

Answer 3

select
    AddressState,
    year(OpenedDate) as Yr,
    month(OpenedDate) as Mnth,
    sum(Payment) as SumPayment
from Customer c
    inner join Account a
     on c.CustomerID=a.CustomerID
    inner join Payment p
     on a.AccountID=p.AccountID
group by AddressState, month(OpenedDate)