# 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?

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…