Query Conundrum - Need help w/ structure to get desired result 1

[laakins]

Basically, I am attempting to combine 2 queries into one. To simplify it, I have a customer table, a Sales table, and a Refund table (whose structure matches the Sales table). I need to report an aggregate by month, returning the following format:

Month Cust ID Cust Name Sales Refunds
------------------------------------------------
Jan 100 Bob's bobbers 50 -30
Feb 100 Bob's bobbers 30 -20
Mar 100 Bob's bobbers 150 -60
...

The query, simplified, I'm using is:

SELECT S.Date, C.Cust ID, C.Cust Name, Sum(S.Sales), Sum(R.Refunds)
FROM Customer AS C
INNER JOIN Sales AS S ON C.Cust ID = S.Cust ID
INNER JOIN Returns AS R ON C.Cust ID = R.Cust ID
GROUP BY S.Date, C.Cust ID, C.Cust Name
HAVING S.Date >= '01/01/2010' AND C.Cust ID = '100'

What I'm getting back is:

Month Cust ID Cust Name Sales Refunds
------------------------------------------------
Jan 100 Bob's bobbers 350 -550
Feb 100 Bob's bobbers 210 -330
Mar 100 Bob's bobbers 1050 -550
...

Again, this is all simplified, but obviously if the simplified version isn't returning the correct values then the full query won't either..

Thanks for the help!

 

[Aluminum]

I don't know exactly what your data looks like, but you may be getting duplicates due to the inner joins.

I typed this up in notepad so the syntax may need to be tweaked. This may fix the duplication:

SELECT Transactions.Date,
C.Cust_ID,
C.Cust_Name,
Sum(Transactions.Sales) as Sales,
Sum(Tranactions.Refunds) as Refunds
FROM Customer AS C
INNER JOIN
(SELECT Cust_ID,
Date
Sales,
0 as Refunds
FROM Sales
WHERE S.Date > '01/01/2010'
UNION SELECT Cust_ID,
Date
0 as Sales,
Refunds
FROM Refunds
WHERE R.Date > '01/01/2010)
as Transactions
ON C.Cust_ID = Transactions.Cust_ID
GROUP BY Transactions.Date,
C.Cust_ID,
C.Cust_Name

 

[laakins]

That worked! I figured it had something to do with the joins, but wasn't sure exactly how to fix it.

Thank you so much!!