Revisiting CASES

Screen Link: Learn data science with Python and R projects

Just asking out of curiosity, why is it that if I write ORDER BY 1 without GROUP BY 1, the results only return one row? I am assuming this is because this is the one customer that has made a one time purchase. The remaining customers who made multiple purchases did not appear in the output due to the aggregate functions, correct?

My Code: SELECT
c.first_name || " " || c.last_name customer_name,
COUNT(i.invoice_id) number_of_purchases,
SUM(i.total) total_spent,
CASE
WHEN SUM(i.total) <40 THEN “small spender”
WHEN SUM(i.total) >100 THEN “big spender”
ELSE “regular”
END
AS customer_category
FROM customer c
INNER JOIN invoice i ON i.customer_id = c.customer_id
GROUP BY 1 ORDER BY 1;

See this blog post to understand why, despite what it may look like, it’s not a good idea to not include the GROUP BY clause.