PostgreSQL - Conditional aggregation - Avg() in Select statement

Question

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;

Inego · Accepted Answer · 2018-03-23 03:00:24Z

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"

David Aldridge · Accepted Answer · 2013-06-20 13:26:11Z

13

How about:

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

answered Jun 20, 2013 at 13:26

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

1 Comment

geoyws Over a year ago

Caution to readers: your avg might be wrong if you intend to exclude the row entirely from your list of numbers. @Inego's answer with FILTER WHERE would be better for that scenario.

user80168 · Accepted Answer · 2013-06-20 12:55:52Z

1

select user, -- very bad choice for column name, but i assume it's just SO example, not real column sum( mark ) / count (nullif(mark, 0)) from table group by user

should so the trick.

answered Jun 20, 2013 at 12:55

user80168

2 Comments

Fridolin Over a year ago

is there also possibility how to avoid division by zero in case 'John' has all 'Marks' zero values?

Fridolin Over a year ago

I think I have worked it out sum( mark ) / nullif( count (nullif(mark, 0)),0) Thanks again for your help.

Collectives™ on Stack Overflow

PostgreSQL - Conditional aggregation - Avg() in Select statement

3 Answers 3

Comments

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Related