Error in answer given by DQ (Nested and correlated subqueries in SQL screen 2)

https://app.dataquest.io/c/111/m/603/nested-and-correlated-subqueries-in-sql/2/filtering-with-correlated-subqueries

I believe the answer given by DQ in this screen is not correct.
The question is to display all invoices but the answer given is aggregating which results in only one line.

At the end of the code I think

GROUP BY invoice_id

should be added, am I correct?

Quite the opposite. Using a GROUP BY would aggregate the answers. I’m for sure missing what is it that you’re missing.

Here’s an alternative solution

SELECT i.*
  FROM invoice AS i
  JOIN (
       SELECT billing_country, AVG(total) AS avg_invoice
	     FROM invoice
		GROUP BY billing_country
     ) AS abc
    ON i.billing_country = abc.billing_country
 WHERE i.total > abc.avg_invoice;

I suggest you begin by thinking through it and then maybe try to share something that may help me understand what you’re missing so that I can help you with the original question.

1 Like

@Bruno You’re right, I made a mistake! Is it possible to delete this post or…?

It’s better if we don’t. You had this question, so other people might have it too. When they have the same question, they can use the tags for locate it and get their question answered rapidly.

Hello, I want to do the sub-query method, but I would like to see the total_avg alias as a end column. Is that possible with the sub-query, not join way of doing it? What I have is the following:

SELECT i1.invoice_id, i1.billing_country, i1.total
FROM invoice i1
WHERE total > (SELECT AVG(total)
FROM invoice i2
WHERE i1.billing_country = i2.billing_country);

The intent is something like:

SELECT i1.invoice_id, i1.billing_country, i1.total, i2.avg_invoice
FROM invoice i1
WHERE total > (SELECT AVG(total) as avg_invoice
FROM invoice i2
WHERE i1.billing_country = i2.billing_country);

However, the SELECT on the outer query cannot access the aggregate function inside and I get an error. What is the solution to getting this column without resorting to the JOIN way of doing it? Thank you.

Hi Andy,

Welcome to the Community!

Could you please create a new topic for this your question?
Thank you and happy learning! :mortar_board:

1 Like