Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multi database Sum Process Expand / Collapse
Author
Message
Posted Monday, July 22, 2013 4:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 3:27 PM
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
Post #1476307
Posted Monday, July 22, 2013 5:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 11:45 AM
Points: 200, Visits: 156
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

Post #1476312
Posted Monday, July 22, 2013 7:52 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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!
Post #1476325
Posted Tuesday, July 23, 2013 2:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 3:27 PM
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


Post #1476409
Posted Tuesday, July 23, 2013 5:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 3:27 PM
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
Post #1476516
Posted Tuesday, July 23, 2013 5:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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!
Post #1476518
Posted Tuesday, July 23, 2013 7:50 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:09 AM
Points: 31, Visits: 275
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.
Post #1476580
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse