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

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_80
FROM
   grades

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

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)
    END
FROM
   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_80
FROM
   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_80
FROM
   grades