Left Join

  • 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

  • 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

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

  • 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...

  • 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 UNION ALL

    SELECT 'name1',1 UNION ALL

    SELECT 'name1',1 UNION ALL

    SELECT 'name1',1 UNION ALL

    SELECT 'name1',1 UNION ALL

    SELECT 'name1',1 UNION ALL

    SELECT 'name1',1 UNION ALL

    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

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

  • 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

  • Thanks call.copse

    That solved the problem.

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

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