CODEWITHSUNDEEP
sqlsql-server
David
David

Reputation: 1580

SQL to return top selling products from database

I have a SQL Query that returns the to 27 selling products from the database:

SELECT TOP 27 ROW_NUMBER() OVER (ORDER BY ttP.ProductName ASC) AS Rno, ttP.*, ttI.ImageID FROM tblProducts AS ttP 
LEFT OUTER JOIN (SELECT ImageID, ProductID FROM tblImages WHERE Main = 1) AS ttI ON ttP.ProductID = ttI.ProductID 
LEFT OUTER JOIN (SELECT TOP 27 ProductID, SUM(Quantity) AS NumSold FROM tblOrderItems GROUP BY ProductID ORDER BY SUM(Quantity) DESC) 
AS ttOI ON ttP.ProductID = ttOI.ProductID 
WHERE ttP.ProductVisibleOnline = 1 AND ttP.CollectionID IS NULL 
AND ttOI.NumSold IS NOT NULL 
ORDER BY ttOI.NumSold DESC, ttP.ProductName ASC

I want to add the following WHERE clause so it returns the top 27 products that have a certain feature

WHERE ttP.ProductID IN (SELECT ProductID FROM tblProductsFeatures WHERE FeatureID = 3)

So I go ahead and add it toward the end of the statement and end up with this:

SELECT TOP 27 ROW_NUMBER() OVER (ORDER BY ttP.ProductName ASC) AS Rno, ttP.*, ttI.ImageID FROM tblProducts AS ttP 
LEFT OUTER JOIN (SELECT ImageID, ProductID FROM tblImages WHERE Main = 1) AS ttI ON ttP.ProductID = ttI.ProductID 
LEFT OUTER JOIN (SELECT TOP 27 ProductID, SUM(Quantity) AS NumSold FROM tblOrderItems GROUP BY ProductID ORDER BY SUM(Quantity) DESC) 
AS ttOI ON ttP.ProductID = ttOI.ProductID 
WHERE ttP.ProductVisibleOnline = 1 AND ttP.CollectionID IS NULL 
AND ttOI.NumSold IS NOT NULL 
AND ttP.ProductID IN (SELECT ProductID FROM tblProductsFeatures WHERE FeatureID = 3)
ORDER BY ttOI.NumSold DESC, ttP.ProductName ASC

This returns 11 records which are a subset of the top 27 selling products without the where clause. I'm not understanding where I can put the where clause so that I get the top 27 selling products with FeatureID = 3. Any help would be greatly appreciated.

Upvotes: 0

Views: 239

Answers (2)

sgeddes
sgeddes

Reputation: 62861

Looking at how you're joining on the tblOrderItems table, perhaps this is what you're looking for instead:

SELECT TOP 27 ROW_NUMBER() OVER (ORDER BY ttP.ProductName ASC) AS Rno, 
              ttP.*, ttI.ImageID 
FROM tblProducts AS ttP 
    LEFT OUTER JOIN (
        SELECT ImageID, ProductID
        FROM tblImages 
        WHERE Main = 1) AS ttI ON ttP.ProductID = ttI.ProductID 
    INNER JOIN (
        SELECT TOP 27 ProductID, SUM(Quantity) AS NumSold 
        FROM tblOrderItems 
        WHERE ProductID IN (SELECT ProductID 
                            FROM tblProductsFeatures 
                            WHERE FeatureID = 3)
        GROUP BY ProductID 
        ORDER BY SUM(Quantity) DESC) AS ttOI ON ttP.ProductID = ttOI.ProductID 
WHERE ttP.ProductVisibleOnline = 1 
    AND ttP.CollectionID IS NULL 
ORDER BY ttOI.NumSold DESC, 
    ttP.ProductName ASC

This also replaces one of your outer joins with an inner join and removes some of the where criteria.

Upvotes: 1

StuartLC
StuartLC

Reputation: 107387

The problem here is that you are finding the top 27 products in a derived table (SELECT TOP 27 ...AS ttOI), but then applying further filters in the outer select. This has the effect of further reducing your product row count from 27, down to 11, since the rows eliminated by the outer filter will not somehow force the inner 27 restriction to be reevaluated against the new criteria.

I would suggest that you do ALL filtering against the eligible products before joining other tables, and ranking the product popularity. Note also that if a product has more than one image, you could have the product duplicated?

SELECT ROW_NUMBER() OVER (ORDER BY ttP.ProductName ASC) AS Rno, 
       ttP.*, ttI.ImageID 
FROM 
  tblProducts AS ttP
  INNER JOIN
  (
    SELECT TOP 27 ttp.ProductId, SUM(ttOI.Quantity) AS NumSold
    FROM tblProducts AS ttP 
      INNER JOIN tblOrderItems ttOI
       ON ttP.ProductID = ttOI.ProductID 
      INNER JOIN tblProductsFeatures tPF
       ON ttP.ProductID = tPF.ProductID
    WHERE ttP.ProductVisibleOnline = 1 
      AND ttP.CollectionID IS NULL 
      AND ttOI.NumSold IS NOT NULL 
      AND tPF.FeatureID = 3
    GROUP BY ttp.ProductID 
    ORDER BY SUM(Quantity) DESC) AS ep -- Eligible products
  ON ttP.ProductID = ep.ProductID
LEFT OUTER JOIN -- Duplicate Rows if there is more than one image here
  tblImages AS ttI
   ON ttP.ProductID = ttI.ProductID 
   AND ttI.Main = 1
ORDER BY ep.NumSold DESC, ttP.ProductName ASC;

Upvotes: 0

Related Questions