SQL aggregate functions

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)

3 thoughts on “SQL aggregate functions”

  3. user

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

Leave a Reply

Your email address will not be published.