i have one table
i would like to classify the apple as below
i try to add code as below, but its not like what I expected
SELECT apple_id, sum(apple_wgt)
,case when apple_colour != 'no colour' then sum(apple_wgt) as 'apple_wgt_no_colour'
,case when apple_colour = 'no colour' then sum(apple_wgt) as 'apple_wgt_colour'
from apples
group by apple_id, apple_wgt
I try this code also, but got error
SELECT apple_id, sum(apple_wgt)
,case when apple_colour != 'no colour' then sum(apple_wgt) end as 'apple_wgt_no_colour'
,case when apple_colour = 'no colour' then sum(apple_wgt) end as 'apple_wgt_colour'
from apples
group by apple_id,
case when apple_colour != 'no colour' then sum(apple_wgt) end,
case when apple_colour = 'no colour' then sum(apple_wgt) end
CodePudding user response:
You want conditional aggregation here, which involves summing over the CASE
expression:
SELECT
apple_id,
SUM(apple_wgt) AS apple_wgt,
SUM(CASE WHEN apple_colour != 'no colour'
THEN apple_wgt ELSE 0 END) AS apple_wgt_no_colour
FROM yourTable
GROUP BY
apple_id;