I have a table sructure like below.
T_Sales
| Bill_ID | Product_ID | Quantity | Price | |
| 1 | A | 2 | $25.00 | |
| 1 | B | 3 | $78.00 | |
| 1 | C | 1 | $18.00 | |
| 2 | A | 4 | $98.00 | |
| 2 | D | 5 | $28.00 | |
| 3 | E | 2 | $105.00 | |
| 4 | D | 3 | $76.00 |
|
.
n-1 E
n D
I need query for Total Price of each product_ID .
i.e, Select Product_Id , Quantity*Price as Total Price
But, if same product is there in diff. bills, Total Price should be (quantity of A in Bill 1*price + quantity of A in bill 2 * price +....)/ (quantity of A in Bill 1 + quantity of A in Bill 2+...).
I hope, you understand my problem. Please let me know query for this requirement.
Thanks
Ranjith
MananPosted Oct 13, 2007, 12:58 AM
You can get total price of the the particular product in different bills as,
SELECT Product_ID,sum(Quantity*Price) "Total Price" FROM T_sales group by Product_ID