SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multi database Sum Process


Multi database Sum Process

Author
Message
volkankoc2000
volkankoc2000
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 16
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
rackerland
rackerland
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 231
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.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17105 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
volkankoc2000
volkankoc2000
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 16
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
volkankoc2000
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 16
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.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17105 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Voide
Voide
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 280
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search