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
)
Question:
What does this part of the code mean?
SELECT count(*)
FROM customer
where country = c.country
) = 1 THEN “Other”
how would country=c.country allow you to sift through to find out the country with only count 1? Could someone help me out? Thank you!
This query joins 3 tables. invoice_line, invoice, customer. invoice was joined solely to connect the other two tables, no columns were selected from it.
The information selected were country, customer_id, il.*. The 1st selection had a CASE to allow overwriting the default country name with ‘Other’ if there was only 1 country of it appearing in the customer table.
The WHERE statement in inner query is where the correlation happens. Correlated subquery means a piece of information from an outer query is used in an inner query. When the outer query goes down the rows and looks at the customer 1 by 1, a new country is supplied to the inner query. This country is used by WHERE in inner query to count how many of this country there are, to output either ‘Other’ or leave the name alone.
Yes I remember first looking at the solution and thought no way i could write this because the lessons never taught correlated subquery. There are probably ways to do it without though.