Answering Business Questions Using SQL 191-5

Screen Link:

My Code:


    country_sales AS
                WHEN COUNT(DISTINCT(c.customer_id)) = 1 THEN "Other"
            END AS country,
            COUNT(DISTINCT(c.customer_id)) tot_cust,
            ROUND(SUM(, 2) tot_sales,
            ROUND(SUM(, 2) avg_sales_per_cust,
            ROUND(SUM(, 2) avg_order_value
        FROM customer c
        INNER JOIN invoice i ON i.customer_id = c.customer_id
        GROUP BY country
        ORDER BY tot_sales DESC
    sorting AS
                WHEN country = "Other" THEN 1
                ELSE 0
            END sort
        FROM country_sales 
        GROUP BY country, sort

FROM country_sales cs
INNER JOIN sorting s ON =
ORDER BY s.sort, tot_sales DESC

What I expected to happen:
Countries that fall under “Other” to add up

What actually happened:

I’ve read a post where it says that I should change the alias name country but when I do that, in my country_sales, I get the error aggregate functions are not allowed in the GROUP BY clause. Is my country_sales doing too much at once and should be broken up into more subqueries? I’m not sure where to go so that the “Other” row comes out correctly.

Would appreciate some help on it, thank you!

1 Like

Hi @spi
It’s been a long time since I laid my hands on this project.
You’ve done a great job, the biggest problem here was in terms of how you understood the statement. Especially this part where I too would have done as you:

  • Where a country has only one customer, collect them into an “Other” group.

It would be better if you break your CTE into a subqueryl, that way, you can know how things are working.

Attached is the Solution Notebook which can help you further in demistifying the statements.