Count how many rows with one column value have another column value greater than 0 postgresql

Let’s say I have a table like so

And I would like to query the table to output all the unique names (John, Mike, and Ryan) as well as the number of Value columns each one has that is greater than 0. (NOT the sum of their value columns).

The output should be like so:

John 2
Mike 1
Ryan 0

So far I have been able to output the unique names with

SELECT "Name" FROM table GROUP BY "Name"

However the rest escapes me.

Thank you!

>Solution :

You can use FILTER():

SELECT "Name" 
    , COUNT(*) FILTER(WHERE "Value" > 0)
FROM table 
GROUP BY "Name"
ORDER BY 2 DESC; -- 2nd column

Advice: Don’t use UPPER case in your table or column definitions. Such a pain in the *** to keep on using " and " forever…