I’m going thru CODE CHALLENGE: AGGREGATE FUNCTIONS: # Code Challenge 1
And just playing around, I’m trying to use CASE statement inside of COUNT to see how can I get the same result as when I use just WHERE.
I got 697 results by using this code:
WHERE email LIKE ‘%.com’;
And 1000 results by using this:
WHEN email LIKE ‘%.com’ THEN 1 ELSE 0 END)
Would appreciate it if you can point me to the mistake
@isoul2007, welcome to the forums!
The difference between your two queries comes down to two things.
First, the order in which SQL executes statements:
SQL executes the
FROM statement and
WHERE statements before the
SELECT statement, so in your first query it has already filtered your
users table down to records where
email is like
%.com (609 records or rows) before it returns the
COUNT of the
In your second query you are trying to narrow the results in a
CASE statement that is within the
SELECT statement. This
CASE statement will be applied at the time of the
SELECT statement, which occurs after SQL has already grabbed all 1000 records in the
users table. Your
CASE statement applies a 1 or a 0 for each entry, but the
COUNT statement will still count each one of those ones and zeros, returning a count of 1000.
COUNT you should have used
SUM on your
CASE statement, which would have added all the ones and zeros and given you the same number as your first query.
Check out the differences in the screenshot below:
Hey @isoul2007, welcome to the forums!!
In the first case you are ONLY selecting the users that have an email that ends with
.com. But it the second case, you are selecting ALL THE USERS, but marking those that have an email that ends with
.com with a
Hope this helps!
Omg, it was such a stupid mistake lol
Thanks a lot @el_cocodrilo and @yizuhi!
Hm, another weird one.
Now related to: https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-calculate-and-summarize-data/modules/analyze-data-sql-practice-aggregate-functions/projects/sql-aggregates-crypto
WHEN currency = 'BIT' THEN 1
ELSE 0 END)
^^^ gives me 42 instead of 21
The goal in this particular exercise is to select two numbers where one of them are coming with an additional condition. Instead of using two queries, I’d like to use just one.
The right answer tho is:
WHERE currency = 'BIT';
Think about what the right answer is counting vs what you are summing.
Then run this query and see if you can figure out what is going on:
SELECT COUNT(*), COUNT(money_in)
Right, yeah. Thanks!
I’m counting all transactions (both in and out), while it clearly says I need just money_in.
Is it possible to modify that
CASE query in a way to sum only money_in events?
Right now your
CASE statement only has one condition. Consider how you could narrow its scope by adding another condition with the use of
AND. Take a look at the data and see if you can figure this out.
If you have tried for a while and just can’t get it, take a peek at the hidden code below:
Don't click here until you've tried on your own!
WHEN money_in NOT NULL AND currency = 'BIT' THEN 1
END) AS bit_in
I was playing with it for a few days but never thought to use “NOT NULL” thing.
Thanks a lot!!
The key was recognizing why
SELECT COUNT(*) and
SELECT COUNT(money_in) return different numbers.
If you examine the first few lines of the table, you can see that the
money_out columns contain NULL (
will only count the records where
is not null. To achieve a similar result with your
statement, you would want to make sure that one of the conditions likewise limits the results to records where
is not null.
Anyway, glad you gave it a good faith effort and I hope this helps you out down the road.