CREATE VIEW top_countries AS
COUNT(DISTINCT c.customer_id) customers,
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
WHEN tc.customers > 1 THEN tc.country
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
anybody who can help with this out there?
Apologies. Missed to respond to you timely
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
If you wish to have a country column first, write your case statement, followed by customer, avg (calculated) columns etc. instead of using
Hope this works for you. Let us know otherwise.
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?