November 16, 2006 at 7:57 am
I want to get the sales amount for each shop, for each of three different products, and for the sum of the three products. The problem is that I only get for each shop for each of the product, but not the sum of the three products OR for each shop the total of the three products, but not for each product.
My query looks like this:
SELECT
Shop_name,
case
when prod in (1,2,3) then ‘milk_bread_butter’
when prod = 1 then ‘milk’
when prod = 2 then ‘bread’
when prod=3 then ‘butter’
else ‘none’
end as product,
sum(sales_amount) as sales
FROM
Dbo.sales
GROUP BY
Shop_name,
case
when prod in (1,2,3) then ‘milk_bread_butter’
when prod = 1 then ‘milk’
when prod = 2 then ‘bread’
when prod=3 then ‘butter’
else ‘none’
end
The problem is that I only get the lin 'milk_bread_butter' per shop. If I switch the order around in the case statement;
case
when prod = 1 then ‘milk’
when prod = 2 then ‘bread’
when prod=3 then ‘butter’
when prod in (1,2,3) then ‘milk_bread_butter’
else ‘none’
I get the sales amounts per shop for each of the products, but not for the milk_bread_butter total.
I would be very greatful for any guidance as how to solve this.
Thank you.
Best, Malte
November 16, 2006 at 9:08 am
After the first condition for CASE is met, the execution jumps out from the CASE. That is the reason why you obtain those results.
November 17, 2006 at 2:52 am
is there any way to work around this, to get the result for the individual products as well as the sum for the three products?
November 17, 2006 at 6:25 am
Please post some sample data and the required output from that data. We won't be able to help without that!
November 17, 2006 at 8:08 am
You might try a union statement:
SELECT Shop_name, ‘milk_bread_butter’ as Product, sum(sales_amount) as sales
FROM Dbo.sales Where prod in (1,2,3) GROUP BY shop_name
UNION
SELECT Shop_name,
case
when prod = 1 then ‘milk’
when prod = 2 then ‘bread’
when prod=3 then ‘butter’
else ‘none’
end as product,
sum(sales_amount) as sales
FROM
Dbo.sales
GROUP BY
Shop_name,
case
when prod in (1,2,3) then ‘milk_bread_butter’
when prod = 1 then ‘milk’
when prod = 2 then ‘bread’
when prod=3 then ‘butter’
else ‘none’
end
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply