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