Lesson 9 HAVING -- Why did HAVING COUNT(price) and HAVING COUNT(downloads) both work?


#1

The instructions are to “Add a HAVING clause to restrict the query to prices where the total number of apps at that price point is greater than 9.”

My first try I tried the following:

SELECT price,
ROUND(AVG(downloads))
FROM fake_apps
GROUP BY price
HAVING COUNT(price) > 9;

It was marked correct, but I honestly just felt lucky I guessed the correct answer rather than confident in my logic.

So I tried to play with it to see how the query results would change:

SELECT price,
ROUND(AVG(downloads))
FROM fake_apps
GROUP BY price
HAVING COUNT(downloads) > 9;

yielded the same results, and was still marked correct.

Why is this?


#2

Going for the simplest query is usually where to start…

SELECT name, price FROM fake_apps
GROUP BY 1
HAVING price > 9;
|name|price|
|---|---|
|Anzoom|14.99|
|E-planet|14.99|
|Hayholding|14.99|
|Highflex|14.99|
|Inch-tone|14.99|
|Ladex|14.99|
|Opetech|14.99|
|Tamp-can|14.99|
|Tampzone|14.99|
|Tinunolane|14.99|
|Zoodom|14.99|
|Zoovivaice|14.99|

The lesson may be marked correct simply because the SCT is looking for HAVING in the query. Did the query return what you expected? Can you refine it and still get the same results?


#3

Yeah I suppose my main concern is that the SCT (I’m guessing this is the code checker….what does “SCT” stand for?) is marking things correct just because of syntax and not because of accurate query results.

Based on my understanding of the wording of the exercise, my expected results should only include price points that had more than 9 apps with downloads to average. In your suggestion, why is the name column necessary, and why is the average downloads not included?

This is the exercise for reference:

Suppose we have the query below:
SELECT price,
ROUND(AVG(downloads))
FROM fake_apps
GROUP BY price;
Certain price points don’t have very many apps, so the average is less meaningful.
Add a HAVING clause to restrict the query to prices where the total number of apps at that price point is greater than 9.


#4

Submission Correctness Test.

It’s a script that the author(s) create specifically for testing the session page. It’s not an exhaustive test, and in most cases looking for keywords or patterns other than results. There is no one way for a lesson to be tested or checked so from lesson to lesson or unit to unit it’s practically impossible to predict what the SCT will be looking at.

The learning environment permits a lot of experimental queries so don’t be afraid to comment out (and label) queries while continuing to run another. (Wait until you have a check mark, though.) Don’t be afraid to enter errors, either. That way you get familiar with the error messages that SQL reports.

Only an example, not a suggestion. I wasn’t sure what lesson you were on and tossed that out there. I believe this is the lesson, is it not?

having

SELECT price, ROUND(AVG(downloads))
FROM fake_apps
GROUP BY price
HAVING COUNT(*) > 9;

From this query we see that there are less than 10 apps at the 3.99 price point.

select price, count(*) from fake_apps
group by 1;

|price|count(*)|
|---|---|
|0.0|73|
|0.99|43|
|1.99|42|
|2.99|21|
|3.99|9|
|14.99|12|


#5

Thanks so much for your response. Yes, the exercise you linked is the one I was working on.

One last question, that kind of circles back to my original question:

SELECT price, ROUND(AVG(downloads))
FROM fake_apps
GROUP BY price
HAVING COUNT(*) > 9;

SELECT price, ROUND(AVG(downloads))
FROM fake_apps
GROUP BY price
HAVING COUNT(price) > 9;

SELECT price, ROUND(AVG(downloads))
FROM fake_apps
GROUP BY price
HAVING COUNT(downloads) > 9;

All three of these variations result in the exact same table. Could you please explain why? Essentially, I’m not understanding what the final HAVING COUNT line in each variation are doing and how they differ from each other.


#6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.