SQL aggregate functions

Question

hello,

I have a similar question to this one http://stackoverflow.com/questions/501347/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)

Answer 1

HAVING (Sum(s.quantity*s.price)) > @X

might do the trick?

Answer 2

deleted (because i misread the comments on the previous answer)

Answer 3

(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)