Nested and Correlated Subqueries Assessment Q3

Q&A DQ Courses
, ,
#1

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?

#2

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

#3

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

#4

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);

#5

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.

#6

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);
#7

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