Nested and Correlated Subqueries Assessment Q3

Screen Link: Learn data science with Python and R projects

My Code: SELECT i1.billing_city
FROM invoice i1
GROUP BY i1.billing_city
HAVING AVG(i1.total) > (SELECT SUM(i2.total)/ COUNT(i2.billing_city)
FROM invoice i2
WHERE i1.billing_city = i2.billing_city)

The question is:

  1. Write a query to display the billing city and its average purchase amount where this average is greater than the average purchase amount in the other billing cities.

What actually happened:
Is incorrect because:

  • The query did not produce the correct table. Column billing_city from table invoice does not have the correct number of values. It should have 29 but it has 325 instead.

How am i supposed to write the subquery of finding the avg sales of all other cities?

Hi,
HAVING applied only after aggregation function.
Hence SELECT must have to contain cont(), sum() and other aggregation function

I don’t understand what you’re trying to suggest?

vadmin,

does this code make sense?

SELECT i1.billing_city
FROM invoice i1
GROUP BY i1.billing_city
HAVING AVG(total) > (SELECT AVG(total)
FROM invoice i2
GROUP BY total);

Example first part query

select billing_city, count(billing_city), AVG(total), sum(total)
from invoice
group by billing_city
HAVING count(billing_city) > 5;

you get table with data

if run this query

SELECT i1.billing_city
FROM invoice i1
GROUP BY i1.billing_city

you get only one column with cities
Appearing clause group by without aggregation function is the redundant in this case.

Shouldn’t the subquery be NOT equals? You have it as equals.

WHERE i1.billing_city <> i2.billing_city

Here is what I put for this answer, but DataQuest is rejecting it because it says it doesn’t have a subquery in the HAVING clause, which … I don’t understand. Would welcome feedback:

SELECT billing_city, AVG(total) AS total_avg
  FROM invoice AS outer
 GROUP BY billing_city
HAVING AVG(total) > (SELECT SUM(inner.total)/COUNT(*)
                       FROM invoice AS inner
                      WHERE inner.billing_city <> outer.billing_city);

inner outer - these are SQL Key Words and using its in the not right context it’s not good idea…