Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
parachuter
Starting Member
2 Posts |
 Posted - 2009-11-04 : 06:09:08
|
Hi, guys, I'm so glad I found this forum.Yesterday I ran into a problem. I have two tables in the database. One 'Product' that stores the product_id and the name of the product, and the other table 'Orders' stores the order_id,product_id(from the 'Product' table),price of the order and quantity of products bought. I'm trying to calculate the profit(SUM(price)) and the total quantity sold(SUM(qty)) of each product, while querying the product name.I've tried:SELECT o.product_id,SUM(o.price),SUM(o.qty) FROM product p,orders o WHERE p.product_name like '%something%' AND p.product_id=o.product_id but this only gives me a single row containing the sums of the product with the first product_id. I'd like to include all the products with their profit and qty sold. I forgot to mention that same product names can have different product ids'.Is there a simple solution to this? I really appreciate your help.Thanks |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-11-04 : 06:14:14
|
| SELECT o.product_id,SUM(o.price),SUM(o.qty) FROM product p,orders o WHERE p.product_id=o.product_id group by o.product_idSenthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
 |
|
|
parachuter
Starting Member
2 Posts |
Posted - 2009-11-04 : 06:40:35
|
| Awesome! Thanks a lot Senthil, that did the trick.Cheers |
|
|
|
|
|
|
|
|
Problem with calculating SUM