CODEWITHSUNDEEP
sqlsql-server
Bernard Polman
Bernard Polman

Reputation: 855

Get sum of two products with specific values

I have a problem where I select products from a table. I also want the query to select the sum of all products from that query if they have a specific value, for example: I want to get the price sum of products that have type = 1 or type = 2. So if I have 100 products and two of them have type =1 or type =2, the first product has price = 10 and the other = 25, then I want to get the value =35 from the query.

SELECT

product.name,
product.type,
product.price

FROM product

Is there a better way to do this than making a separate subquery ? The query example above is very barebones and the actual case where I'll need this is a much bigger query and using a subquery will greatly impact the performance.

Upvotes: 0

Views: 898

Answers (3)

LukStorms
LukStorms

Reputation: 29677

If grouping by product alone is fine, then you can include conditional aggregations.

SELECT p.name
, SUM(p.price) AS TotalProductPrice
, AVG(p.price) AS AverageProductPrice
, SUM(CASE WHEN p.type IN (1, 2) THEN p.price END) AS TotalProductPriceOneTwoTypes
, AVG(CASE WHEN p.type IN (1, 2) THEN p.price END) AS AverageProductPriceOneTwoTypes
FROM product AS p
GROUP BY p.name

And then you can SUM OVER a normal SUM.

SELECT p.name
, SUM(p.price) AS TotalProductPrice
, SUM(CASE WHEN p.type IN (1, 2) THEN p.price END) AS TotalProductPriceOneTwo
, SUM(SUM(CASE WHEN p.type IN (1, 2) THEN p.price END)) OVER () AS TotalPriceOneTwoAllProducts
FROM product AS p
GROUP BY p.name

test

Upvotes: 0

KekuSemau
KekuSemau

Reputation: 6853

You can use a window function. This allows to aggregate over all returned rows even when the query does not use grouping.

SELECT
    product.name,
    product.type,
    product.price,
    SUM(product.price) OVER() TotalPrice
FROM product

The true power of window functions is that you can also return aggregates for sub-groups, eg. include the total price for each type:

, SUM(product.price) OVER(PARTITION BY product.type) TotalPricePerType

This will show the total price for all rows with type=1, type=2 etc.

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133400

If you just need the sum you could try

 select  sum(price)
 from my_table 
 where type in (1,2)

Upvotes: 1

Related Questions