RedJohn RedJohn -4 years ago 61
SQL Question

SQL Server 2005 MAX, SUM AND GROUP BY

I am using SQL Server 2005 and I have a problem with my SQL query. Basically, I want to get the total amount of all transactions of customers, grouped by company, based on the latest dates from all customer transactions.

Sample data:

Customer_Id Date Amount COMPANY
-------------------------------------------------
1 3/3/2014 9021 COMPANY X
2 3/3/2014 12000 COMPANY Y
2 3/15/2014 10000 COMPANY Y
2 3/30/2014 8000 COMPANY Y
4 3/13/2014 10000 COMPANY Z
5 3/14/2014 1400 COMPANY X
1 3/16/2014 2500 COMPANY X
7 3/14/2014 110 COMPANY Y
3 3/17/2014 1500 COMPANY Z
2 3/19/2014 2044 COMPANY Y
3 3/09/2014 9400 COMPANY Z
3 3/11/2014 8950 COMPANY Z
2 3/31/2014 3455 COMPANY Y
3 3/15/2014 950 COMPANY Z
6 3/15/2014 5543 COMPANY X


What I want to accomplish is like this:

COMPANY TOTAL
COMPANY X 9443 --> sum from customer_id 1 (2500, as of 3/16/2014) and customer_id 6 (5542, 3/15/2014) and customer_id 5 (1400 as of 3/14/2014)
COMPANY Y 3455 --> sum from customer_id 2 (3455, as of 3/31/2014)
COMPANY Z 10950 --> sum from customer_id 4 (1000, as of 3/13/2014) and customer_id 3 (950, as of 3/15/2014)


Below are some SQL queries I've tried which doesn't work on my goal:

SELECT TOP (1) WITH TIES
Date, Company, SUM(Amount) AS total
FROM
tbl_Table
GROUP BY
Date, Company
ORDER BY
Date DESC

SELECT
t1.Date, t1.Company, SUM(t1.Amount) AS total
FROM
tbl_Table AS t1
INNER JOIN
(SELECT
MAX(Date) AS date, Company
FROM
tbl_Table
GROUP BY
Company) AS t2 ON t1.Date = t2.Date AND t1.Company = t2.Company
GROUP BY
t1.Date, t1.Company


WITH latest AS
(SELECT
Company, MAX(Date) AS maxdate
FROM
tbl_Table
GROUP BY
Company
)
SELECT
a.Date, a.Company, SUM(a.Amount) AS total
FROM
tbl_Table AS a
INNER JOIN
latest AS b ON a.Company = b.Company AND a.Date = b.maxdate
GROUP BY
a.Date, a.Company

Answer Source

Your results are still not correct by data you give.

SQLFIDDLEExample

Query:

SELECT t1.Company,
       SUM(t1.Amount) Total
FROM Table1 t1
   LEFT JOIN Table1 t2
    ON t1.COMPANY = t2.COMPANY
      AND t1.Customer_Id = t2.Customer_Id
      AND t1.Date < t2.Date
   WHERE t2.Customer_Id is null
GROUP BY t1.Company

Result:

|   COMPANY | TOTAL |
|-----------|-------|
| COMPANY X |  9443 |
| COMPANY Y |  3565 |
| COMPANY Z | 11500 |
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download