CREATE VIEW country_other AS SELECT *, CASE WHEN (SELECT COUNT(c.customer_id) FROM customer c WHERE country = c.country) = 1 THEN "Other" ELSE c.country END AS new_country FROM customer c; WITH country_info AS (SELECT c.country "Country", COUNT(DISTINCT c.customer_id) "Number of Customers", COUNT(DISTINCT i.invoice_id)"Number of Orders", ROUND(SUM(i.total),2) "Total Sales", ROUND(SUM(i.total)/COUNT(DISTINCT c.customer_id),2) "Avg sales/customer", ROUND(SUM(i.total)/COUNT(DISTINCT i.invoice_id),2) "Avg order value" FROM customer c LEFT JOIN invoice i on c.customer_id = i.customer_id GROUP BY Country ORDER BY "Total Sales" DESC )
I have some doubts about this question in the guided project specifically, but I cannot understand what am I doing wrong in my particular case.
My original idea was to create a new view with the new_country column, where I can do the Country/Other organization, and then utilize this new view with country_info. I have run each separately to test, and I can achieve the right data from the country_info (but without the Other assignment) for now. However, when I look into my new view country_other, I don’t have any “Other” being assigned.
However, so far I could not understand the flaw in my logic, so if anyone could point me out to the (obvious) mistake, I would appreciate it! Thanks