| Author |
 Topic  |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
 Posted - 25 April 2005 : 08:04:30
|
I've pulled most of my hair out trying to build a query
I'm looking to get the following returned:
ProductID | ProductName | ProductVariant | VariantUnitType | 'Units sold' | 'Product Sales'
First 4 columns are just listed from the Products table, but its the last two derived columns that are twisting my melon:
'Units sold' would be the total value of all the Quantity fields in tblOrderItem, where they have a matching productID
'Product sales' would be the total value of Quantity multiplied by the price fields in tblOrderItem, where they have a matching productID
Here are the table structures:
I've tried several approaches to get this data, but in all cases I get duplicate ProductID in the results, which I just cant understand. |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
-gary
Development Team Member
406 Posts |
Posted - 25 April 2005 : 12:26:06
|
Like this?
select P.ProductId, P.ProductName, P.ProductVariant, P.VariantUnitType, (select sum(Quantity) as Quantity from tblOrderItem Q where Q.ProductId = P.ProductID) as 'Units Sold', (select sum(Price * Quantity) from tblOrderItem S where S.ProductID = P.ProductID) as 'Product Sales' from tblProducts P |
KawiForums.com
 |
 |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 25 April 2005 : 17:23:54
|
Thanks Gary, Your post completed the puzzle! I was using subqueries, but hadn't referenced the productID's inside correctly.
 |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 27 April 2005 : 13:43:23
|
Without subselects
select P.ProductId, P.ProductName, P.ProductVariant, P.VariantUnitType, sum(OI.Quantity) as 'Units Sold', sum(OI.Price * OI.Quantity) as 'Product Sales'
from tblProducts P left join tblOrderItem OI on P.ProductID=OI.ProductID
group by P.ProductId, P.ProductName, P.ProductVariant, P.VariantUnitType |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 27 April 2005 : 14:26:26
|
| That looks a lot like what I had originally, but for some reason, I kept seeing duplicate productID's in the results. Maybe the data isn't quite correct in places. |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
| |
Topic |
|
Topic Locked
