%%sql
CREATE VIEW top_countries AS
SELECT
c.country,
COUNT(DISTINCT c.customer_id) customers,
SUM(i.total) amount_sold,
SUM(i.total)/COUNT(*) avg_order,
SUM(i.total)/ COUNT(DISTINCT c.customer_id) avg_sales_per_customer
FROM customer c
INNER JOIN invoice i ON i.customer_id = c.customer_id
GROUP BY 1
ORDER BY 2 DESC
SELECT *
FROM (
SELECT
tc.*,
CASE
WHEN tc.customers > 1 THEN tc.country
ELSE 'Others'
END AS country
FROM top_countries tc
)
ORDER BY customers DESC;
What I expected to happen:
I expect the values in the ‘country’ column to update to ‘Other’ when customer count is <=1
What actually happened:
A new ‘country:1’ column is created
Hi guys, can someone help with the above? Been stuck for days
You have included tc.*, in your final query. Since the country column already exists as the first column, the CASE statement leads to a column with the same name. So SQL auto-updates that to the country1 column.
If you wish to have a country column first, write your case statement, followed by customer, avg (calculated) columns etc. instead of using tc.*.
Thank you @Rucha. Sorry for the late response I’ve been out a few weeks.
‘Since the country column already exists as the first column, the CASE statement leads to a column with the same name. So SQL auto-updates that to the country1 column.’
Why doesn’t the same happen with the with statement? Because you are not creating a new entity inside the db?