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)
GROUP BY price;
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
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:
On first glance, it looks like it works, but really all you did was rename
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
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:
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.
WITH high_dl AS (
WHERE downloads > 17000
ROUND(AVG(downloads)) AS 'Average Downloads',
COUNT(*) AS '# of apps'
GROUP BY price;