Left Join

Question

Post reply

Left Join

rootfixxxer

SSCrazy

Points: 2918
More actions
February 10, 2011 at 3:15 am

#231158

Hello
Need a quick help, i have two tables, that are related by a varchar field, in the first table i have 43 lines that are related with 2 lines in the second table, i need to make a query that shows the name of the related field, the sum of the values for the first table and another column with the sum of the values in the second table.
The problem it's in the second sum, it multiples the value of the sum by the number of lines that exist in the first table. If the value of the sum is 12 (6 for each line), the column will show 516 (43 lines * 12).
I know the problem it's in the query because i missing something, that i don't recall now...
The query simplified:
SELECT DISTINCT T1.colName, SUM(T1.ValCol1), SUM(T2.ValCol2)
FROM Table1 T1 LEFT OUTER JOIN Table2 T2 ON T1.colName=T2.colName
GROUP BY T1.colName
Thanks

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Is it possible to provide us with sample input data and the desired output data?

That way it is more clear what you want to achieve.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

brainy SSCrazy Points: 2445 More actions · Answer 2

Yes, it is helpful if you can provide us sample data.

rootfixxxer SSCrazy Points: 2918 More actions · Answer 3

I know that i need to put the data here with the SELECT UNION to help to test the query, but i don't find the topic how to do it...

rootfixxxer SSCrazy Points: 2918 More actions · Answer 4

Here some sample data:

GO

CREATE TABLE #Table1 (colName VARCHAR(10),ValCol1 DECIMAL)

CREATE TABLE #Table2 (colName VARCHAR(10),ValCol2 DECIMAL)

INSERT INTO #Table1

SELECT 'name1',1 UNION ALL

SELECT 'name1',1

INSERT INTO #Table2

SELECT 'name1',2 UNION ALL

SELECT 'name1',2

SELECT DISTINCT T1.colName,SUM(T1.ValCol1),SUM(T2.ValCol2)

FROM #Table1 T1 LEFT OUTER JOIN #Table2 T2 ON T1.colName=T2.colName

GROUP BY T1.colName

GO

In fact the two sums are wrong, the results should be 10 and 4, and i have 20 (10 * 2lines) and 40 (4 * 10 lines).

Thanks

SQLSmasher Right there with Babe Points: 777 More actions · Answer 5

Apology, it will be more understandable if you provide sample data and table structure.

call.copse SSCoach Points: 18047 More actions · Answer 6

rootfixxxer (2/10/2011)
Hello
Need a quick help, i have two tables, that are related by a varchar field, in the first table i have 43 lines that are related with 2 lines in the second table, i need to make a query that shows the name of the related field, the sum of the values for the first table and another column with the sum of the values in the second table.
The problem it's in the second sum, it multiples the value of the sum by the number of lines that exist in the first table. If the value of the sum is 12 (6 for each line), the column will show 516 (43 lines * 12).
I know the problem it's in the query because i missing something, that i don't recall now...
The query simplified:
SELECT DISTINCT T1.colName, SUM(T1.ValCol1), SUM(T2.ValCol2)
FROM Table1 T1 LEFT OUTER JOIN Table2 T2 ON T1.colName=T2.colName
GROUP BY T1.colName
Thanks

I would expect something like:

SELECT T1.colName, sumt1, sumt2 FROM

(SELECT colName, SUM(ValCol1) as sumt1 FROM

Table1 GROUP BY colName) T1

LEFT OUTER JOIN

(SELECT colName, SUM(ValCol2) as sumt2 FROM

Table2 GROUP BY colName) T2

ON T1.colName=T2.colName

to do what you are looking for.

cheers,

Iain

rootfixxxer SSCrazy Points: 2918 More actions · Answer 7

rootfixxxer

SSCrazy

Points: 2918

February 10, 2011 at 4:29 am

#1285072

Thanks call.copse

That solved the problem.