FAQ: Conditional Aggregates - COUNT(CASE WHEN )

Frequently Asked Questions SQL FAQ
1

Community%20FAQs%20on%20Codecademy%20Exercises
Community%20FAQs%20on%20Codecademy%20Exercises1000×208 141 KB

This community-built FAQ covers the “COUNT(CASE WHEN )” exercise from the lesson “Conditional Aggregates”.

Paths and Courses
This exercise can be found in the following Codecademy content:

SQL: Table Transformation

FAQs on the exercise COUNT(CASE WHEN )

There are currently no frequently asked questions associated with this exercise – that’s where you come in! You can contribute to this section by offering your own questions, answers, or clarifications on this exercise. Ask or answer a question by clicking reply (reply) below.

If you’ve had an “aha” moment about the concepts, formatting, syntax, or anything else with this exercise, consider sharing those insights! Teaching others and answering their questions is one of the best ways to learn and stay sharp.

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

2

I think that this exercise can also be done using subqueries as in the first lesson:

SELECT state,
   (SELECT COUNT(*)
   FROM airports AS a
   WHERE a.elevation < 1000
   AND a.state = airports.state) AS count_low_elevation_airports
FROM airports
GROUP BY state;

Is there any reason to prefer using COUNT(CASE WHEN) over this?

3

Your code doesn’t count blank states

4

Could anyone explain me the logic of the query ? how does “state” and counted elevations connect if we don’t use ‘where’ statement ?

5

The GROUP BY relates the COUNT with the ‘state’ column. The table has a ‘state’ value and ‘elevation’ value for each row. So they are already “connected”. The query tells you how many airports in each state are considered “low elevation”. without the GROUP BY you just get a total of all low elevation airports.

6

Does anybody else feel that this particular course hasn’t really helped them learn SQL, its just copying code and changing it slightly. Highly doubt this is how its done in the real world lol. Its been good up to now

1 Like
7

lol so i’m pretty sure i got the equation right, but it tells me that i’m wrong and throws me a total non-sequitur solution? super weird.

my code:

select state, count(case when elevation <= 1000 then 1 else null end) as count_low_elevation_airports from airports group by 1;

but then it tells me this is the solution:

SELECT COUNT(*) FROM flights WHERE arr_time IS NOT NULL and destination = 'ATL';