1

I have a similar question to this one SQL products/productsales

I want to do that same query but instead of just checking quantity i want to check "total" (ie. quantity * price). "price" is a field in the sales table.

here is the original query suggested on that link:

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 )

so instead of Sum(s.quantity) i need to have (s.quantity*s.price) for EACH SALE to be added up and then compared to @X. (the price can be different for each sale)

Improve this question
1
  • Could you insert your query and the error message please? You can edit your question. Commented Feb 4, 2009 at 9:13

2 Answers 2

Reset to default
1 
HAVING (Sum(s.quantity*s.price)) > @X

might do the trick?

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

i'm busy testing this. it seems right. i will edit this comment a bit later. thanks!
it seems i can't edit comments :) sorry about the spam. anyway i have edited my question. your original answer works if the price is the same for every sale.
0

(The answer from Cagcowboy was marked as accepted, but the comment seems to indicate it didn't work, so this is another approach)

This code uses a derived table to first work out the "totals" for each product, then the grouping etc. is layered over the top of that.

SELECT p.name from products p
WHERE p.product_id IN
    (SELECT product_id from 
        (SELECT product_id, (quantity * price) as total
         FROM productsales WHERE date between @dateStart and @dateEnd) as s
    GROUP by s.product_id
    HAVING sum(total) > @x)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.