Multi database Sum Process

Question

Post reply

Multi database Sum Process

volkankoc2000

Grasshopper

Points: 19
More actions
July 22, 2013 at 4:55 pm

#295722

There are 3 table
this table
I want to as a result
CUSTOMER----PAYMENT------------ORDER
Name ------ SUM(TOTAL)--------SUM(TOTAL)
Jean -------- 1500----------------- 500
Ali ---------1000------------------250
This will make the process the query..
thank you
English is a little bad

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

rackerland Old Hand Points: 359 More actions · Answer 1

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

Dwain Camps SSC Guru Points: 86908 More actions · Answer 2

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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

volkankoc2000 Grasshopper Points: 19 More actions · Answer 3

Thanks for the answers

but the result is wrong

my datebase sqlite

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

volkankoc2000 Grasshopper Points: 19 More actions · Answer 4

dwain.c (7/22/2013)
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 database sqlite

Dwain Camps SSC Guru Points: 86908 More actions · Answer 5

volkankoc2000 (7/23/2013)
my database sqlite

As this is a MS SQL forum, I couldn't help but make the assumption that your db is MS SQL.

I can't imagine there'd be much syntactical difference between MS SQL and SQLLite (althought I know nothing of the latter) because there certainly isn't anything special going on in either of the 2 examples.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

Voide Mr or Mrs. 500 Points: 548 More actions · Answer 6

The first thing that pop'd into my head was that the temp tables are probably declared differently. Sure enough http://www.sqlite.org/tempfiles.html shows that temp tables are declared with the syntax "CREATE TEMP TABLE".

I feel like the OP could have googled this too, but there you go. Try changing the syntax around for Dwain's code and it may work. Other syntax between the two looks fairly normal.... although I am not familiar with SQLite at all.