Group countries with 1 customer to other

Hi! Does anyone have the same question?
I dont understand the case when statement in the with as table. Inside the case when There is a subquery SELECT count(*) FROM customer where country = c.country What is the logic for the where statement? Why we have c.country in the subquery? we only have one table from customer from the sub query, correct? and why we put subquery = 1?

Thank you!!

Screen Link:

My Code:

WITH country_or_other AS
    (
     SELECT
       CASE
           WHEN (
                 SELECT count(*)
                 FROM customer
                 where country = c.country
                ) = 1 THEN "Other"
           ELSE c.country
       END AS country,
       c.customer_id,
       il.*
     FROM invoice_line il
     INNER JOIN invoice i ON i.invoice_id = il.invoice_id
     INNER JOIN customer c ON c.customer_id = i.customer_id
    )

hi @candiceliu93

This response from Bruno explains this query in detail.

1 Like