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

To multiply two coluumns which are in two different columns

P: 5
I have table items
| iid | iname | quantity | did | price_kg |
| 1 | rice | 40 | d2 | 2 |
| 2 | sugar | 20 | d2 | 16 |
| 3 | dall | 30 | d1 | 20 |
| 3 | dall | 30 | d3 | 20 |
| 3 | dall | 30 | d2 | 20 |

I have another table soldtable
| iid | pid | soldquantity |
+------+------+--------------+
| 1 | p1 | 5 |
| 1 | p2 | 6 |
| 2 | p1 | 4 |
| 2 | p2 | 5 |
| 3 | p1 | 5 |
| 3 | p2 | 9 |

I want the result as
| pid | iid | bill(items.price_kg*soldtable.soldquantity)|
+------+------+--------------------------------------------+
| p1 | 1 | 10(2*5) |
| p1 | 2 | 64(16*4)|
| p1 | 3 | 100(20*5)|
| p2 | 1 | 12(2*6) |
| p2 | 2 | 80(16*5)|
| p2 | 3 | 180(20*9)|

So please provide the query for this
Mar 28 '15 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 1,031
If you have this in our items table:
| iid | iname | quantity | did | price_kg |
| 3 | dall | 30 | d1 | 20 |
| 3 | dall | 30 | d3 | 20 |
| 3 | dall | 30 | d2 | 20 |

and this in your soldtable:
iid | pid | soldquantity |
+------+------+--------------+
| 3 | p1 | 5 |
| 3 | p2 | 9 |

Than how would you calculate the result if the prices of this 3 line in the items table are NOT the same?

back to the question, to start with:
Expand|Select|Wrap|Line Numbers
  1. SELECT pid,iid,items.price_kg*soldtable.soldquantity
  2. FROM soldtable, items
  3. WHERE soldtable.iid = items.iid
  4.  
I need answer on my question to give more complete answer ;)
Mar 29 '15 #2

Post your reply

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