HAVING with AS 'names'

Link to the exercise with the database
My own task: run must returns # of average downloads per app and # of apps grouped by price, that have >17000 downloads

I tried two variants, both don’t work correctly:

1st

SELECT price, 
  ROUND(AVG(downloads)) AS 'downloads', 
  COUNT(name) AS '# of apps'
FROM fake_apps
GROUP BY price
HAVING downloads > 17000;

2nd:

SELECT price, 
  ROUND(AVG(downloads)) AS 'downloads', 
  COUNT(name) AS '# of apps'
FROM fake_apps
GROUP BY price
HAVING 'downloads' > 17000;

Please help to figure out.

Hi @zmeyok, welcome to the forums!

Have you learned about CTEs (temporary tables created with the WITH...AS syntax) yet?
If so, you can use that knowledge to solve your challenge. If not, you may want to come back to this problem after you’ve done that section and see if you can figure it out then.

Of course, if you still can’t figure it out after learning about CTEs, feel free to ask again and we can help you out :slight_smile:

1 Like

What line of code could you use to count all of the apps at each price point?

Just completed the course, it works:

WITH prev_query AS (
  SELECT price, 
  ROUND(AVG(downloads)) AS 'downloads', 
  COUNT(name) AS '# of apps'
FROM fake_apps
GROUP BY price
)
SELECT *
FROM prev_query
WHERE downloads > 17000;

But I haven’t got how to select renamed columns correctly.
If I write the same code but replace * with
SELECT price, downloads, # of apps
the query won’t return a result.

I suppose this part of code calculates average downloads in each price category:

SELECT price, ROUND(AVG(downloads))
FROM fake_apps
GROUP BY price;

So your new query doesn’t exactly work yet. The reason is the same reason it didn’t work right the first time: you are grouping BEFORE you are filtering by the downloads > 17000 condition.

Let’s take a look at what happens to your downloads columns when you GROUP BY price.

Here, we have your first query, but without the HAVING downloads > 17000, and adding in the downloads column so you can see what you were filtering by.

SELECT price, downloads, ROUND(AVG(downloads)), COUNT(name)
FROM fake_apps
GROUP BY price;

image

So what are these numbers in downloads? They are the last entries for downloads at that price point (you can see this if you SELECT * FROM fake_apps and scroll to the bottom).

When you use GROUP BY, any column that wasn’t an aggregate (such as AVG, SUM or COUNT) or in the GROUP BY clause still needs to have values. In this case, SQLite chose to fill them in with the last downloads value at each price point.

This is what you were filtering by when you were saying HAVING downloads > 17000, which would only return the row for 1.99, since it’s the only one that shows a downloads value greater than 17000.

Now, in your most recent query, you introduce a change:

image

On first glance, it looks like it works, but really all you did was rename ROUND(AVG(downloads)) to downloads, so you are now filtering that column by the > 17000 condition. If you scroll up in this post, you’ll see that your results are just the last three rows of the price, ROUND(AVG(downloads)), and COUNT(name) columns from above.

Again, this is because we grouped before we filtered. If we filter out the apps with more than 17000 downloads BEFORE we group, our result looks like this:
image

This is the average download count and number of apps per price point out of all the apps that were downloaded more than 17000 times.

See if you can figure out how to achieve this by filtering before you group. If you try for 30 minutes and still can’t get it, feel free to check out the solution below.

Happy coding!

WITH high_dl AS (
  SELECT * 
  FROM fake_apps
  WHERE downloads > 17000
)
SELECT price, 
  ROUND(AVG(downloads)) AS 'Average Downloads',
  COUNT(*) AS '# of apps'
FROM high_dl
GROUP BY price;

Wow, that helps, thank you. But could you please also say how to solve issue in my last code sample, where I can use only SELECT * and not SELECT price, downloads, # of apps

You just have to put quotes around the column name # of apps. You have to do this any time your column name has spaces.

So you would write SELECT price, downloads, '# of apps'

2 Likes