By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 921 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

To subtract two columns which are in two different tables

P: 5
I have 2 tables, items and sold . In items table I have itemid , item name , quantity and in sold table I have itemid , peopleid, itemname , soldquantity. I want the output as itemid, itemname,remquantity(quantity-soldquantity) and where will the final output will store?
Mar 17 '15 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,347
Join the two tables on itemid and do the subtraction there.

You shouldn't store the final output. It is better to create it as a view so the data is always up to date.
Mar 17 '15 #2

Expert 100+
P: 1,031
Is your only question where to store the output?
Because that answer depends on your needs, and Rabit gave a good answer already.....

But if your question is also about how to retrieve the data from MySQL than, please give the SQL statement you tried but 'did not work for this purpose'
Mar 17 '15 #3

P: 5
can u please provide sql query to my question?
Mar 18 '15 #4

Rabbit
Expert Mod 10K+
P: 12,347
Please make an attempt at the code using the algorithm provided. If you run into any problems, post the query you tried and any error messages you get.
Mar 18 '15 #5

P: 5
Expand|Select|Wrap|Line Numbers
  1. select items.iid, SUM(items.quantity)-sum(soldtable.soldquantity) as remquantity from items inner join soldable on items.iid = soldtable.iid group by iid,pid;
  2. My tables are:
  3. items:
  4. iid    iname    quantity
  5. 1    Rice    40
  6. 2    Sugar    20
  7. 3    Dall    30
  8. soldtable:
  9. iid    Pid    Soldquantity
  10. 1    P1    5
  11. 1    P2    6
  12. 2    P1    4
  13. 2    P2    5
  14. I'm getting the output as:
  15. iid    Remquantity
  16. 1    35
  17. 1    34
  18. 2    16
  19. 2    15
  20. But I want the output as 
  21. iid    remquantity
  22. 1    29
  23. 2    11
please give me query for this one.
Mar 19 '15 #6

Rabbit
Expert Mod 10K+
P: 12,347
1) Don't group by pid
2) Use the max of the items quantity, not the sum
Mar 19 '15 #7

P: 5
Ok sir its working. Thank you for your help.
Mar 19 '15 #8

Post your reply

Sign in to post your reply or Sign up for a free account.