Guided Project: Answering Business Questions using SQL - yet another Analyzing Sales by Country question

CREATE VIEW country_other AS
    SELECT *,
                     FROM customer c
                     WHERE country = = 1 THEN "Other"
            END AS new_country
    FROM customer c;


country_info AS
    (SELECT "Country",
        COUNT(DISTINCT c.customer_id) "Number of Customers",
        COUNT(DISTINCT i.invoice_id)"Number of Orders",
        ROUND(SUM(,2) "Total Sales",
        ROUND(SUM( c.customer_id),2) "Avg sales/customer",
        ROUND(SUM( 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 :smile:

Im not the best at SQL.
I edited your selection a bit to help for Country_Other.
Heres what I came up with (although probably to late to help):

CREATE VIEW country_other AS
SELECT country,
COUNT(customer_id) AS number_of_customers,
WHEN COUNT(DISTINCT c.customer_id) = 1
THEN ‘Other’
ELSE Country
END AS new_country
FROM customer c
GROUP BY country;
#Then if you run the next bit you will see where those single markets are.
FROM country_other
ORDER BY country;