Thread: SQL Query Help ....... Again :-)

Greetings All,

I'm using the following query to pull the 3 bestselling Products over the past 7 days for a particular category.

C#/VB.NET Code:

    <query name="Products" rowElementName="Product">
        <sql>
            <![CDATA[
SELECT DISTINCT TOP 3
    s.ProductID,
    s.NumSales,
    s.Numpounds
FROM (

select ProductID, SUM(Quantity) AS NumSales, SUM(OrderedProductPrice) AS Numpounds
      from dbo.Orders_ShoppingCart

 sc
            join [dbo].Orders o on sc.OrderNumber = o.OrderNumber and o.OrderDate >= dateadd(dy, -7, getdate())
      group by ProductID, VariantID
     ) s
    join [dbo].Product p with (nolock) on s.productid = p.productid
WHERE p.Deleted = 0
    and p.Published = 1  
    and p.ProductID in ( select ProductID from dbo.ProductCategory where CategoryID = @parent )
ORDER BY s.NumSales desc
            ]]>
        </sql>
        <queryparam paramname="@parent" paramtype="runtime"   requestparamname="parent" sqlDataType="int" defvalue="0" validationpattern="" />
    </query> 


My problem is that it sometimes returns multiple entries for the same product - because the best sellers are variants of the same product.

How would I modify the query to only return one entry for each product ID?


TTFN

BFG


P.S. Nearly done with the new design then I'll have something cool to show you all............

Won't removing the

, VariantID

from your inner select statement aggregate the like productIDs together?

One one of our databases I get the following as your code is now:
ProductID NumSales Numpounds
7881 6 77.70
7881 5 64.75
4658 4 67.80

While by commenting out the ", VariantID" from the inner select statement I get:
ProductID NumSales Numpounds
7881 11 142.45
4658 4 67.80
4586 2 29.90

Which seems correct, no?

Revised code:

C#/VB.NET Code:

SELECT DISTINCT TOP 3 
    s.ProductID, 
    s.NumSales, 
    s.Numpounds 
FROM ( 

select ProductID, SUM(Quantity) AS NumSales, SUM(OrderedProductPrice) AS Numpounds 
      from dbo.Orders_ShoppingCart 

 sc 
            join [dbo].Orders o on sc.OrderNumber = o.OrderNumber and o.OrderDate >= dateadd(dy, -7, getdate()) 
      group by ProductID-- , VariantID 
     ) s 
    join [dbo].Product p with (nolock) on s.productid = p.productid 
WHERE p.Deleted = 0 
    and p.Published = 1   
    and p.ProductID in ( select ProductID from dbo.ProductCategory where CategoryID = 26 ) 
ORDER BY s.NumSales desc 


You are of course quite correct - Thank You!


TTFN

BFG