rackerland (7/22/2013)
If I understand what you're asking for correctly, this should do it:
SELECT C.Name, SUM(P.Total), SUM(O.Total)FROM Customer AS C
LEFT OUTER JOIN [Order] AS O
ON O.CustomerID = C.CustomerID
LEFT OUTER JOIN Payment AS P
ON P.CustomerID = C.CustomerID
GROUP BY C.Name
I believe that there is a flaw in this logic. Take a look at this:
CREATE TABLE #Customer
(
ID INT IDENTITY
,Name VARCHAR(20)
);
CREATE TABLE #Order
(
ID INT IDENTITY
,CUSTOMERID INT
,Total MONEY
);
CREATE TABLE #Payment
(
ID INT IDENTITY
,CUSTOMERID INT
,Total MONEY
);
INSERT INTO #Customer SELECT 'Ali' UNION ALL SELECT 'Mary';
INSERT INTO #Order
SELECT 1, 1000 UNION ALL SELECT 1, 2000
UNION ALL SELECT 2,1500 UNION ALL SELECT 2,2500;
INSERT INTO #Payment
SELECT 1, 2000 UNION ALL SELECT 1, 4000
UNION ALL SELECT 2,3500 UNION ALL SELECT 2,4500;
SELECT CUSTOMERID, TotalOrders=SUM(Total) FROM #Order GROUP BY CUSTOMERID;
SELECT CUSTOMERID, TotalPayments=SUM(Total) FROM #Payment GROUP BY CUSTOMERID;
SELECT C.Name, TotalPayments=SUM(P.Total), TotalOrders=SUM(O.Total)
FROM #Customer AS C
LEFT OUTER JOIN #Order AS O
ON O.CustomerID = C.ID
LEFT OUTER JOIN #Payment AS P
ON P.CustomerID = C.ID
GROUP BY C.Name;
SELECT C.Name, TotalPayments=SUM(P.Total), TotalOrders=SUM(O.Total)
FROM #Customer AS C
LEFT OUTER JOIN (
SELECT CUSTOMERID, Total=SUM(Total)
FROM #Order
GROUP BY CUSTOMERID
) AS O ON O.CustomerID = C.ID
LEFT OUTER JOIN (
SELECT CUSTOMERID, Total=SUM(Total)
FROM #Payment
GROUP BY CUSTOMERID
) AS P ON P.CustomerID = C.ID
GROUP BY C.Name;
GO
DROP TABLE #Customer
DROP TABLE #Order
DROP TABLE #Payment
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St