Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Wednesday, April 27, 2011 7:17 PM
Hi to All again.
How to subtract two values(records) from same table but different columns?
SqlCommand cmd = new SqlCommand("SELECT * FROM [dbo].tblProducts (neededInStock) - (presentInStock) as diff ", con);
SqlDataAdapter sqlDa1 = new SqlDataAdapter();Dan
Wednesday, April 27, 2011 7:19 PM ✅Answered | 1 vote
Try
"SELECT *,neededInStock - presentInStock as [diff] FROM [dbo].tblProducts "
The resulting table will have one extra DIFF column with the difference.
For every expert, there is an equal and opposite expert. - Becker's Law
Wednesday, April 27, 2011 7:20 PM ✅Answered
use
"SELECT *, neededInStock - presentInStock as diff FROM [dbo].tblProducts"
as the SQL.
you will get an extra column Diff as the last column in the resultset.
The simpler the solution the stronger it is
If this post answers you, please mark it as answer..
If this post is useful, please vote it as useful..
Wednesday, April 27, 2011 7:27 PM
I need to add additional Diff column?or this is name of function? Dan
Wednesday, April 27, 2011 7:31 PM
The diff is just the name of the computed column in your select. So you could do (for example)
SELECT *, neededInStock - presentInStock as RequiredStock,
presentInStock * AverageCost As InventoryValue
FROM [dbo].tblProducts"
Tom
Wednesday, April 27, 2011 7:34 PM
Hi Naomi.
If I use this code I recieve value from first table.
I put the picture of my sql table here : http://rjxysw.blu.livefilestore.com/y1pCWbF2Lgn8E0wVLFVkbFHXVV95Vl-ymUJKYXyiMWDMuHMApFn13Iow98VweaR3UKs4aJJYzDPULf_5IJqOwV-A1JR7CpioQPa/SQL%20table..jpg?psid=1
Dan
Wednesday, April 27, 2011 7:38 PM
sorry of my question but I very new in sql .
I need to add all this column?RequiredStock and InventoryValue
Dan
Tuesday, May 3, 2011 7:22 PM
Dan,
Does the code I provided work for you? I also see from the attached picture that this table doesn't adhere to the normalization rules. Categories and Product Names should be in different own tables.
For every expert, there is an equal and opposite expert. - Becker's Law
Thursday, August 30, 2018 10:15 AM
Hi!
Thank you for the above query. But now I want to do Sum of the new column "Diff".
How to do this?
Regards,
Sunday, September 30, 2018 9:31 PM
Hi,
It may be better to start a new thread with this question. I think you may either use CTE for the first query and then you can reference that new column in the query from cte or you can use SUM(expression) directly in the query.
It may be better to elaborate on your question in a separate thread.
For every expert, there is an equal and opposite expert. - Becker's Law
Sunday, September 30, 2018 9:33 PM
Inventory Value sounds like a complex thing. The value will depend on the calculation method (for example, it can be first in, first out type of calculation).
For every expert, there is an equal and opposite expert. - Becker's Law
Sunday, September 30, 2018 10:12 PM | 3 votes
Hello,
I had a similar request on another forum and here was the best answer.
"Hello, I need to subtract columns C and B (C-B) from a table. Here is the query to create the table. CREATE TABLE My_TABLE1 ( A int, B nvarchar(10), C nvarchar(10) ) INSERT INTO My_TABLE1 VALUES (12,'--','8'), (7,'2', '.03'), (3, '.03','4'), (10, '3','5'), (18, '--','7') Here is the output. SELECT * FROM My_TABLE1 ![alt text][1] When subtract 'C - B' the fourth column will show the difference. ![alt text][2] [1]: /storage/temp/3921-result.png [2]: /storage/temp/3922-result2.png Thank you for your help."
Answer:
"Something like this should work: SELECT [a], [b], [c], CASE [b] WHEN '--' THEN b ELSE CAST(CAST(c AS DECIMAL(5,2)) - CAST(b AS DECIMAL(5,2)) AS NVARCHAR(5)) END AS Diff_C_B FROM <g class="gr_ gr_133 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="133" id="133">dbo</g>.My_Table1 go It's worth noting though if you are going to store numbers and do math on them it would be better to store them at numeric data types (int, decimal, numeric, etc) <g class="gr_ gr_131 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del gr-progress" data-gr-id="131" id="131">other wise</g> you'll have to do a CAST/CONVERT on them with the values you have in the [B] column. Hope that helps."
Good luck
Saturday, October 13, 2018 1:46 PM | 1 vote
Perfect
Wednesday, May 29, 2019 3:25 AM
should diff be in square brackets or not in any brackets
Wednesday, May 29, 2019 3:28 AM
The as should be all capitals!!