Q:

PostgreSQL - Conditional aggregation - Avg() in Select statement

I have this table
| user         | Mark       | Points   |
|--------------|------------|----------|
| John         |    0       |    2     |
| Paul         |    5       |    3     |
| John         |    4       |    4     |
| Paul         |    7       |    5     |

I would like to build a query with one select statement that returns the rows shown below.
Avg(Mark) - should be average only if Mark>0
Sum(Points) - should be sum of all records.
| user         | Avg(Mark)  | Sum(Points) |
|--------------|------------|-------------|
| John         |    4       |    6        |
| Paul         |    6       |    8        |

Can anyone point to a proper syntax? 
I believe it should like :
select user, avg(Mark>0), sum(Points) from Table group by user;

A:

Starting with version 9.4, PostgreSQL directly supports filtering aggregates.
https://www.postgresql.org/docs/9.4/static/sql-expressions.html

If FILTER is specified, then only the input rows for which the filter_clause evaluates to true are fed to the aggregate function; other rows are discarded. 

By using it, your example can be rewritten as:
SELECT
    "user",
    AVG(mark) FILTER (WHERE mark > 0),
    SUM(points)
FROM
    "table"
GROUP BY
    "user"

A:

How about:
select user,
       avg(case when mark > 0 then mark end),
       sum(mark)
from   ...

