%%sql WITH country_sales AS ( SELECT CASE WHEN COUNT(DISTINCT(c.customer_id)) = 1 THEN "Other" ELSE c.country END AS country, COUNT(DISTINCT(c.customer_id)) tot_cust, ROUND(SUM(i.total), 2) tot_sales, ROUND(SUM(i.total)/COUNT(DISTINCT(c.customer_id)), 2) avg_sales_per_cust, ROUND(SUM(i.total)/COUNT(i.invoice_id), 2) avg_order_value FROM customer c INNER JOIN invoice i ON i.customer_id = c.customer_id GROUP BY country ORDER BY tot_sales DESC ), sorting AS ( SELECT country, CASE WHEN country = "Other" THEN 1 ELSE 0 END sort FROM country_sales GROUP BY country, sort ) SELECT cs.* FROM country_sales cs INNER JOIN sorting s ON s.country = cs.country GROUP BY cs.country ORDER BY s.sort, tot_sales DESC
What I expected to happen:
Countries that fall under “Other” to add up
What actually happened:
I’ve read a post where it says that I should change the alias name
country but when I do that, in my
country_sales, I get the error
aggregate functions are not allowed in the GROUP BY clause. Is my
country_sales doing too much at once and should be broken up into more subqueries? I’m not sure where to go so that the “Other” row comes out correctly.
Would appreciate some help on it, thank you!