CODEWITHSUNDEEP
sqlinner-join
user61252
user61252

Reputation:

SQL products/productsales

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)

Upvotes: 0

Views: 686

Answers (4)

Russ Cam
Russ Cam

Reputation: 125488

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

Upvotes: 0

AnthonyWJones
AnthonyWJones

Reputation: 189457

 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 )

Upvotes: 1

Joe Soul-bringer
Joe Soul-bringer

Reputation: 3304

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;

Upvotes: 0

Frederik Gheysels
Frederik Gheysels

Reputation: 56934

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
)

Upvotes: 1

Related Questions