Hey,
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:
SELECT COUNT(email)
FROM users
WHERE email LIKE ‘%.com’;
And 1000 results by using this:
SELECT COUNT(
CASE
WHEN email LIKE ‘%.com’ THEN 1 ELSE 0 END)
FROM users;
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 email column.
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.
Instead of 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 1.
Hope this helps! 
No problem 
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
SELECT SUM(
CASE
WHEN currency = 'BIT' THEN 1
ELSE 0 END)
FROM transactions;
^^^ 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:
SELECT COUNT(money_in)
FROM transactions;
SELECT COUNT(money_in)
FROM transactions
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)
FROM transactions;
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:
SELECT SUM(
CASE
WHEN money_in NOT NULL AND currency = 'BIT' THEN 1
ELSE 0
END) AS bit_in
FROM transactions;
Brilliant.
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_in and money_out columns contain NULL (0) values:
COUNT(money_in) will only count the records where money_in is not null. To achieve a similar result with your CASE statement, you would want to make sure that one of the conditions likewise limits the results to records where money_in is not null.
Anyway, glad you gave it a good faith effort and I hope this helps you out down the road.
Happy coding!