Problems with Group By on Postgresql

Question

I’m trying to make a view in a Postgre database:

CREATE VIEW anuncio_points
AS
    SELECT
    a.*,
    (CASE WHEN p.points is null THEN 0 ELSE sum(p.points) END) + u.points as points
    FROM anuncios as a
    LEFT JOIN users as u ON u.id = a.user_id
    LEFT JOIN points as p ON p.user_id = u.id
    GROUP BY a.id;

But it is returning the following error:

ERROR:  column "p.points" must appear in the GROUP BY clause or be used in an aggregate function
LINE 5:     (CASE WHEN p.points is null THEN 0 ELSE sum(p.points) EN...

I’ve searched hundreds of site but I only find the command for Mysql I enable/disable this function:

SET sql_mode = 'ONLY_FULL_GROUP_BY';

If it is possible to resolve the error with a command like this last what would be its equivalent in Postgresql?

If it’s some other kind of mistake, does anyone have a solution? I’ve wasted a few hours on it...

Answer 1

If you use the sum in the p.points and in the u.points solves their problem, since they are not in the group by.

CREATE VIEW anuncio_points AS SELECT
    a.*,
    ( SUM(p.points)  + SUM(u.points)) AS points
FROM
    anuncios AS a
LEFT JOIN users AS u ON
    u.id = a.user_id
LEFT JOIN points AS p ON
    p.user_id = u.id
GROUP BY
    a.id;