CODEWITHSUNDEEP
postgresqlgroup-by
omega
omega

Reputation: 43833

How to aggregate on first value in postgres sql?

In this postgres query

SELECT
    q.*,
    q.user_id = 1 mine,
    first(u.username) username,
    sum(case when v.answer=0 then 1 else 0 end) no_count,
    sum(case when v.answer=1 then 1 else 0 end) yes_count
FROM question q 
JOIN "user" u ON q.user_id=u.id
JOIN vote v ON v.question_id=q.id
GROUP BY q.id
ORDER BY q.date_created desc 
LIMIT 20
OFFSET 0

It says I need an aggregate on username. In this case, I am getting questions, and each question is created by some user. I join on votes, but group by the question id, so before grouping the username field will be the same for each unique question. So that means when aggregating, really I can pick any value since it's all the same. However I can't find any aggregate function to use. I tried random, first, last, any, but none work or even defined.

Does anyone know how to handle this?

Upvotes: 3

Views: 3913

Answers (2)

Alexander Shcheglakov
Alexander Shcheglakov

Reputation: 157

From the box postgresql do not allow or cant do such operations, but there is workaround.

You can extend aggregate functions First/Last by this method: https://wiki.postgresql.org/wiki/First/last_(aggregate)

There is plugin and portable sql version for postgresql.

Upvotes: 1

Gabriel's Messanger
Gabriel's Messanger

Reputation: 3298

Ok, so problem is that sql engine doesn't know that username will be always the same. In other words it can't tell relation one-to-one from one-to-many between guestions and users. You could use string_agg() with DISTINCT like string_agg(DISTINCT u.username, ','::text). What that functions does is it's aggragetes text values into one big string seperated by specified delimiter (in my case comma). When you add DISTINCT it takes only unique values. Since you've said theres always only one username per question the output will be that single value.

The whole query:

SELECT
    q.*,
    q.user_id = 1 mine,
    string_agg(DISTINCT u.username, ','::text) username,
    sum(case when v.answer=0 then 1 else 0 end) no_count,
    sum(case when v.answer=1 then 1 else 0 end) yes_count
FROM question q 
JOIN "user" u ON q.user_id=u.id
JOIN vote v ON v.question_id=q.id
GROUP BY q.id
ORDER BY q.date_created desc 
LIMIT 20;

Footnote: I dropped the OFFSET 0 because it's default value for LIMIT.

Upvotes: 4

Related Questions