9. Grouping with Case Statements


#1

Simple question, not sure what the subquery is doing exactly in this code. More specifically, how it creates the percentage from order_items using a single table and sum(amount_paid) twice.

select
case name
when 'kale-smoothie' then 'smoothie'
when 'banana-smoothie' then 'smoothie'
when 'orange-juice' then 'drink'
when 'soda' then 'drink'
when 'blt' then 'sandwich'
when 'grilled-cheese' then 'sandwich'
when 'tikka-masala' then 'dinner'
when 'chicken-parm' then 'dinner'
else 'other'
end as category, round(1.0 * sum(amount_paid) /
(select sum(amount_paid) from order_items) * 100, 2) as pct
from order_items
group by 1
order by 2 desc;


#2

The way I understand it...

The subquery is creating the denominator of the fraction that calculates the percentage of sales for a particular category. "Round(1.0 * sum(amount_paid)" is the numerator while "select sum(amount_paid) from order_items)" is the denominator. That calculation is then multiplied by 100 to get the percentage . It is then rounded to two places.

For example, if the sum of amount_pd for the dinner category only is $400 and the total sum for amount_pd for ALL categories is $8200, this part of the query and the subquery are basically saying:

Take 400/8200 (equals around 0.049).
Multiply the decimal returned by 100 (equals 4.9%) to get the percentage.
Round the answer to two places (equals 4.90%). --This is what is returned in the pct column as to what percent of total sales comes from the dinner category.

Hope that helps.


#3

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.