Click here to Skip to main content
15,041,788 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Good afternoon, I have two tables with the same structure and I would like to subtract one from the column (quantity) but I am not getting it, it follows as tables.

|idProduct|amount|classification|
|---------|------|--------------|
|1|1|A|
|1|18|AA|

|idProduct|amount|classification|
|---------|------|--------------|
|1|1| A|
|1|16|AA|


What I have tried:

I try:

SELECT p.[idProduct], ((IsNull(p.[amount], 0)) - IsNull(s.[amount], 0)) AS inStock, p.classification FROM table1 AS p
LEFT JOIN table2 AS s ON p.[idProduct] = s.[idProduct]
GROUP BY p.[idProduct], p.[classification], p.[amount], s.[amount]

|idProduct|amount|classification|
|---------|------|--------------|
|1|0|A|
|1|-15|A|
|1|17|AA|
|1|2|AA|

only the first and last lines are correct, but I don’t know how to remove the others 
Posted
Updated 3-Apr-21 8:12am
v2

1 solution

GROUP BY criteria don't work the way you think: each time you add another field it increases the number of rows you will get in the results, not "compacts" them.

See here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^] it might help you understand how it works.

But you need to think about what you are trying to get - a JOIN is probably what you actually want, with just a ID=ID AND Classification=Classification - but I have no idea what your real data looks like or what you expect to get from it, so I can't be sure.
   
Comments
Maykon Luiz Matos Araujo 4-Apr-21 10:04am
   
Thank you very much, now I saw what was missing.
AND p.classification=s.classification in LEFT JOIN table2 AS s ON p.[idProduct] = s.[idProduct].

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900