Have Geek Brain

Sum subquery and group by customer information

I have three tables something like this:

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

A payment table can contain multiple payments for an account, and a Customer can have multiple accounts.

What I would like to do is get the total of all payments from the government by state and month by month. For instance.

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 which states paid the most for each month. I'm only interested in the month of OpenedDate, but I'm getting it as the processing date later. I was trying to get all the data I need in one request.

I've tried something like:

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 contains some general information in the Account table. The query won't work because a.AccountID is not included in the aggregate function.

Am I approaching this correctly? How can I get the data I need to calculate which customers are paying the most?

+1


a source to share


3 answers


If you want 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 displays the month number (1-12) and the total for each condition. Note that this example does not include years: all amounts for month 1 are added up regardless of the year. Add datepart (yy, OpenedDate) if you like.

+2


a source


In other words, I'm trying to figure out which states paid the most for each month.

This one will select the most favorable condition 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 a list of all states, as in your result set:

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
+1


a source 


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)
0


a source






More articles:


All Articles