-1

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!

Goffer
  • 370
  • 1
  • 3
  • 14
  • Why is the same car id repeated multiple times in the first table? – MatBailie Sep 22 '22 at 11:07
  • thank you for your comment, is is just an example reflecting the connection and the exact problem; query has several dozen joins and first table has 200 columns that do not matter much – Goffer Sep 22 '22 at 11:14
  • 1
    Make a better example. Create a sample dataset, for both tables, and specify the exact results you want for that sample dataset. As described here; [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/q/333952) (Also read the included links on what makes a good Minimal Reproducible Example.) – MatBailie Sep 22 '22 at 11:16
  • 1
    Does this answer your question? [Why do multiple-table joins produce duplicate rows?](https://stackoverflow.com/questions/23786401/why-do-multiple-table-joins-produce-duplicate-rows) – Luuk Sep 22 '22 at 11:26

1 Answers1

1

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.

MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Thank you for the solution, after joining the table as in the solution shown, how should i bring up the value i want to show? t2.Number AS Type_3? – Goffer Sep 22 '22 at 11:45
  • And what should I do if I want to show it for a different type of data, e.g. 1,2,4? – Goffer Sep 22 '22 at 11:51
  • @goffer You need to clarify those requirements in your question. Read this https://stackoverflow.com/help/minimal-reproducible-example and then update your question with... An example dataset for both tables and the Exact results you want for that example dataset (covering every scenario you need addressing, as if you're writing test coverage). Trying to communicate your needs via comments is too vague and too easy to misunderstand. (It also implicitly means that the question is incomplete and should be edited to include full details.) – MatBailie Sep 22 '22 at 18:17
  • I understand, thank you for the information, it is possible that you are right and I did not specify it precisely enough (range 1-5). Above solution allowed me to move forward a bit. Thank you. – Goffer Sep 23 '22 at 06:11