tags:

views:

248

answers:

4

A common (i assume?) type of query:

I have two tables ('products', 'productsales'). Each record in 'productsales' represents a sale (so it has 'date', 'quantity', 'product_id'). How do i efficiently make a query like the following:

Retrieve the product names of all products that were sold more than X times between date Y and date Z.

(X is the quantity sold not the number of transactions)

+1  A: 
 SELECT p.[name]
 FROM products p
 WHERE p.product_id in (SELECT s.product_id
     FROM productsales s
     WHERE s.[date] between @dateStart and @dateEnd
     GROUP BY s.product_id
     HAVING Sum(s.quantity) > @X )
AnthonyWJones
I presume you'll have to use SUM instead of COUNT, since the Sales table has a qty field ... :)
Frederik Gheysels
Fredrik, The question asks for the number of times sold, not the number of items.
Mehrdad Afshari
The question is perhaps vague on this it asks for times but includes the detail that a qty field is available. I've ask Mota for more clarity
AnthonyWJones
Mota has clarified the question, I have edit answer to match
AnthonyWJones
this is exactly what i needed - thanks
Mota
+1  A: 

The above query is not entirely correct ...

SELECT Name FROM Products
WHERE ProductId IN
( SELECT ProductId 
  FROM ProductSales
  WHERE ProductSales.Date BETWEEN  Y AND Z 
  GROUP BY ProductId
  HAVING SUM(ProductSales.Qty) > x
)
Frederik Gheysels
Note most answers are view by votes so 'above' is arbitary. Also it depends on how you interpret the questions use of the phrase "sold more than X times". You could be right it might refer to qty sold but it could also refer to the number of specific transactions.
AnthonyWJones
Note that the "above query" is going to change in this thread over time, making your "above query" reference incorrect...
Joe Soul-bringer
I was referring to the query written by Mehrdad Afshari; which couldn't even be interpreted since it has incorrect syntax.
Frederik Gheysels
I would seem your original interpretation of the question was correct +1.
AnthonyWJones
Fredrik, I have tried the query on SQL Server 2008 and there is no "incorrect syntax" in it.
Mehrdad Afshari
A: 

Actually, I think you should learn the group by statement

The simplest statement would be:

SELECT ps.product_id, COUNT(*) 
           FROM product_sales ps 
           WHERE ps.product_id = id AND ps.Date BETWEEN @Y AND @Z
           GROUP_BY product_id;
Joe Soul-bringer
A: 
SELECT p.[name]
FROM
products p
INNER JOIN
productsales ps
ON 
p.id = ps.product_id
WHERE
ps.date BETWEEN @Y AND @Z
GROUP BY
p.[name] 
--,p.id if it is likely that you have two products with the same name
HAVING
SUM(ps.quantity) > @X

Tested successfully with MS AdventureWorks DB, using the following-

DECLARE @Y DATETIME, @Z DATETIME, @X INT    

SET @Y = '2004-06-09'
SET @Z = '2004-08-01'
SET @X = 100

    SELECT p.[Name]
    FROM
    Production.Product p
    INNER JOIN
    Sales.SalesOrderDetail ps
    ON 
    p.ProductID = ps.ProductID
    WHERE
    ps.ModifiedDate BETWEEN @Y AND @Z
    GROUP BY p.Name
    HAVING
    SUM(ps.OrderQty) > @X
Russ Cam
Assumption: product name are unique?
AnthonyWJones
True, I have made that assumption
Russ Cam
I have updated by including commented out p.id, in the event that two products have the same name. I don't think that I've ever come across this though
Russ Cam