Click here to Skip to main content
15,886,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.
 
Share this answer
 
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)



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