How to correctly sum the values for a unique ID with the condition

Goffer 0
Viewed 37

How to sum the values in Number field in table2 when the ID field is unique and field Type = 3 (range 1-5)?

   SELECT TOP (100)
          TB1.CarID
    FROM table1 as TB1
    LEFT JOIN table2 as TB2 on TB1.CarID = TB2.CarID
    WHERE
    GROUP BY
  1. Table1

enter image description here

  1. Table2

enter image description here

I have tried several solutions:

,SUM (CASE WHEN TB2.Type = 3  THEN TB2.Number END)

Return result is incorrect x2, possibly due to a large number of table joins. If you have any comment why the values are displayed x2, please give me a hint. I would like to add an additional condition, i.e. sum only when the value in the id column is unique. I believe this can solve the problem.

,SUM (CASE WHEN TB2.Type = 3 AND TB2.ID is UNIQUE THEN TB2.Number END) [incorrect]

I will be grateful for your help!

sql
1 Answers

Part of the problem is likely that you join on carid, but that is not unique in Either table.

At present you have 5 copies of 'aaaad' in Table1 and 5 copies in Table2. When you join them, you get 25 rows back (each row in T1 matching against 5 rows in T2).

So, I'd start with aggregating the second table before joining on it...

SELECT
  *
FROM
  table1 AS t1
LEFT JOIN
(
  SELECT
    carid,
    SUM(number) AS number
  FROM
    table2
  WHERE
    type = 3
  GROUP BY
    carid
)
  AS t2
    ON t1.CarID = t2.CarID

That way you avoid multiplying the number of rows.

MatBailie0
Related