locked
calculate is incorrect in join views RRS feed

  • Question

  • User-1687766116 posted

    Hi,

    I have 2 view in SQL Server:

    an1:

    SELECT        id_card, SUM(in1) AS Expr1
    FROM            dbo.an1
    GROUP BY id_card

    an2:

    SELECT        id_card, SUM(out1) AS Expr1
    FROM            dbo.an2
    GROUP BY id_card

    when i Join this views, calculate is incorrect, 

    SELECT        dbo.an1.id_card, SUM(dbo.an1.in1) AS Expr1, SUM(dbo.an2.out1) AS Expr2
    FROM            dbo.an1 INNER JOIN
                             dbo.an2 ON dbo.an1.id_card = dbo.an2.id_card
    GROUP BY dbo.an1.id_card

    an1 and an2 in alone is correct, but when i Join this views, calculate is incorrect,

    how i do?

    Friday, April 15, 2016 2:26 PM

Answers

  • User753101303 posted

    Hi,

    The JOIN is done first (possibly matching multiple rows) and then only it is GROUPed so more likely the same value is included in the sum multiple times. Instead you want to group and then join the results. So in short I believe you want something such as :

    SELECT Group1.id_card,SumIn1,SumOut1
    FROM (SELECT Id_card,SUM(in1) AS SumIn1 FROM an1 GROUP BY id_card) Group1
    JOIN (SELECT id_car,SUM(out1)AS SumOut1 FROM an2 GROUP BY id_Card) Group2 ON Group2.id_card=Group1.Id_Card

    You could use also a CTE with might better show the 3 step approachs (group first results, group 2nd results and then join those results).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 15, 2016 3:05 PM

All replies

  • User-2010311731 posted

    Just a note, many USA organizations block foreign IP addresses.  Until this forum starts allowing direct uploads, I can't see your images.  It would be much better for you to copy and paste your examples into an Insert Code block.

    Sorry I couldn't help!

    Friday, April 15, 2016 2:38 PM
  • User-1687766116 posted

    Just a note, many USA organizations block foreign IP addresses.  Until this forum starts allowing direct uploads, I can't see your images.  It would be much better for you to copy and paste your examples into an Insert Code block.

    Sorry I couldn't help!

    post edited. thanks

    Friday, April 15, 2016 2:50 PM
  • User753101303 posted

    Hi,

    The JOIN is done first (possibly matching multiple rows) and then only it is GROUPed so more likely the same value is included in the sum multiple times. Instead you want to group and then join the results. So in short I believe you want something such as :

    SELECT Group1.id_card,SumIn1,SumOut1
    FROM (SELECT Id_card,SUM(in1) AS SumIn1 FROM an1 GROUP BY id_card) Group1
    JOIN (SELECT id_car,SUM(out1)AS SumOut1 FROM an2 GROUP BY id_Card) Group2 ON Group2.id_card=Group1.Id_Card

    You could use also a CTE with might better show the 3 step approachs (group first results, group 2nd results and then join those results).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 15, 2016 3:05 PM
  • User77042963 posted

    The join condition you are using may be many-to-many, which causes the incorrect result.

    Friday, April 15, 2016 3:34 PM
  • User-1687766116 posted

    Hi,

    The JOIN is done first (possibly matching multiple rows) and then only it is GROUPed so more likely the same value is included in the sum multiple times. Instead you want to group and then join the results. So in short I believe you want something such as :

    SELECT Group1.id_card,SumIn1,SumOut1
    FROM (SELECT Id_card,SUM(in1) AS SumIn1 FROM an1 GROUP BY id_card) Group1
    JOIN (SELECT id_car,SUM(out1)AS SumOut1 FROM an2 GROUP BY id_Card) Group2 ON Group2.id_card=Group1.Id_Card

    You could use also a CTE with might better show the 3 step approachs (group first results, group 2nd results and then join those results).

    how to use wherein this code?

    where Group1.datefa1>13950101

    Friday, April 15, 2016 5:39 PM
  • User-1687766116 posted

    i have a field "datefa1" in an1 and an2 table,

    how to use "where"?

    where Group1.datefa1>13950101

    Friday, April 15, 2016 5:42 PM
  • User-1687766116 posted

    when i use this code:

    result is ok.

    SELECT        dbo.an1.id_card, dbo.an1.name_card, SUM(DISTINCT dbo.an1.in1) AS in1, SUM(DISTINCT dbo.an2.out1) AS out1, SUM(dbo.an1.in1 - dbo.an2.out1) AS bag
    FROM            dbo.an1 INNER JOIN
                             dbo.an2 ON dbo.an1.id_card = dbo.an2.id_card
    WHERE        (dbo.an1.datefa1 >= @d1) and dbo.an1.id_card =@card
    GROUP BY dbo.an1.id_card, dbo.an1.name_card

    Do I do it???

    Friday, April 15, 2016 6:03 PM