CODEWITHSUNDEEP
sql-servert-sql
Disti
Disti

Reputation: 1513

T-SQL: Group two tables, then join

I have two tables (OldVals and NewVals), both with the same format:

PK ProductID  int
PK CustomerID int
   Value      decimal

Some sample data:

OldVals:

ProductID   CustomerID  Value
1           1000        10
2           1000        20
3           1000        30
1           2000        40
4           2000        50
2           3000        60
3           3000        70
4           3000        80

NewVals:

ProductID   CustomerID  Value
1           1000        50
2           1000        60
1           2000        70
2           3000        80
3           3000        90

I'm trying to query these two tables and obtain: CustomerID, Sum of values for each customer in OldVals, Sum of values for each customer in NewVals. ProductIDs are ignored.

The result for the given sample data would be:

CustomerID  SumOld  SumNew
1000        60      110
2000        90      70
3000        210     170

With this query:

select
    OldVals.CustomerID,
    sum(distinct OldVals.Value),
    sum(distinct NewVals.Value) 

from
    OldVals full outer join NewVals on OldVals.CustomerID = NewVals.CustomerID

group by OldVals.CustomerID, NewVals.CustomerID

I obtain the right result for the given sample data, but of course if "Value" field is the same for two rows of the same customer, then the query gives a wrong result, because rows with the same value (within a customer) are summed only once.

Just to clarify, If I replace the first row of OldVals with:

ProductID   CustomerID  Value
1           1000        20

the result should be:

CustomerID  SumOld  SumNew
1000        70      110
...

but I obtain:

CustomerID  SumOld  SumNew
1000        50      110

Any advice?

Thank you!

Upvotes: 1

Views: 3288

Answers (2)

Fuzzy
Fuzzy

Reputation: 3810

You can achieve it either via two select statements as Giorgo has it or a Common Table Expression:

;WITH A
     AS
        (SELECT CustomerID, SUM(Value) AS SumOld
         FROM OldVals
         GROUP BY CustomerID
        ),
     B
     AS
        (SELECT CustomerID, SUM(Value) AS SumNew
         FROM NewVals
         GROUP BY CustomerID
        )
     SELECT A.CustomerID, A.SumOld, B.SumNew
     FROM A
     FULL OUTER JOIN
     B
     ON A.CustomerID = B.CustomerID;

Result:

enter image description here

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use:

SELECT t1.CustomerID , oldSum, newSum
FROM (
   SELECT CustomerID, SUM(Value) AS oldSum
   FROM OldVals
   GROUP BY CustomerID ) AS t1  
FULL OUTER JOIN (
   SELECT CustomerID, SUM(Value) AS newSum
   FROM NewVals
   GROUP BY CustomerID  
) AS t2 ON t1.CustomerID  = t2.CustomerID

The idea is to first GROUP BY each table, then do a FULL JOIN on the already aggregated sets.

Upvotes: 5

Related Questions