In Redshift/Postgres, how to count rows that meet a condition?

First, the issue you're having here is that what you're saying is "If the grade is less than 70, the value of this case expression is count(rank). Otherwise, the value of this expression is count(rank)." So, in either case, you're always getting the same value.

SELECT     CASE        WHEN grade < 70 THEN COUNT(rank)        ELSE COUNT(rank)    ENDFROM   grades

count() only counts non-null values, so typically the pattern you'll see to accomplish what you're trying is this:

SELECT     count(CASE WHEN grade < 70 THEN 1 END) as grade_less_than_70,    count(CASE WHEN grade >= 70 and grade < 80 THEN 1 END) as grade_between_70_and_80FROM   grades

That way the case expression will only evaluate to 1 when the test expression is true and will be null otherwise. Then the count() will only count the non-null instances, i.e. when the test expression is true, which should give you what you need.

Edit: As a side note, notice that this is exactly the same as how you had originally written this using count(if(test, true-value, false-value)), only re-written as count(case when test then true-value end) (and null is the stand in false-value since an else wasn't supplied to the case).

Edit: postgres 9.4 was released a few months after this original exchange. That version introduced aggregate filters, which can make scenarios like this look a little nicer and clearer. This answer still gets some occasional upvotes, so if you've stumbled upon here and are using a newer postgres (i.e. 9.4+) you might want to consider this equivalent version:

SELECT    count(*) filter (where grade < 70) as grade_less_than_70,    count(*) filter (where grade >= 70 and grade < 80) as grade_between_70_and_80FROM   grades

Another method:

SELECT     sum(CASE WHEN grade < 70 THEN 1 else 0 END) as grade_less_than_70,    sum(CASE WHEN grade >= 70 and grade < 80 THEN 1 else 0 END) as grade_between_70_and_80FROM   grades

Works just fine in case you want to group the counts by a categorical column.

The solution given by @yieldsfalsehood works perfectly:

SELECT    count(*) filter (where grade < 70) as grade_less_than_70,    count(*) filter (where grade >= 70 and grade < 80) as grade_between_70_and_80FROM    grades

But since you talked about NULLIF(value1, value2), there's a way with nullif that can give the same result:

select count(nullif(grade < 70 ,true)) as failed from grades;