PGSQL: Analyze > Control dashboard trigger SQL error

[yjacolin]

There are two issues:

First column "measure_id" in group by is ambiguous, and then attributes in SELECT clause and in ORDER BY should be in GROUP BY or use in an aggregate function.

select "c1"."id" as "control_id", "c1"."name" as "name", "c1"."clause" as "clause", "c1"."measure_id" as "measure_id", "c1"."domain_id" as "domain_id", "c1"."plan_date" as "plan_date", "c1"."realisation_date" as "realisation_date", "c1"."score" as "score", "c2"."plan_date" as "next_date", "c2"."id" as "next_id" from "controls" as "c1" left join "controls" as "c2" on "c1"."next_id" = "c2"."id" where "c2"."realisation_date" is null and "c1"."next_id" is not null and "c1"."realisation_date" <= 2023-07-27 group by "measure_id" order by "clause" asc

[dbarzin]

Should be fixed. Thanks, Didier

[yjacolin]

thanks, the ambiguous column error is fixed but I still get

Grouping error: 7 ERROR: column "c1.id" must appear in the GROUP BY clause or be used in an aggregate function

Attributes in SELECT clause and in ORDER BY should be in the GROUP BY or use in an aggregate function in PostgreSQL. I don't know how it works in MySQL!

[dbarzin]

Could you give me the correct SQL command in PostgreSQL ?

[yjacolin]

That's a challenge :) Actually, MySQL use DISTINCT clause for attributes in SELECT not in the group by and not using aggregate function. But PostgreSQL doesn't do this, and DISTINCT is not an aggregate function in PostgreSQL.

So, we can use aggregate function like max() or so and add "clause" attribute in the GROUP BY:

SELECT 
    max("c1"."id") AS "control_id",
    string_agg("c1"."name", ',') AS "name",
    "c1"."clause" AS "clause", 
    "c1"."measure_id" AS "measure_id", 
    max("c1"."domain_id") AS "domain_id", 
    max("c1"."plan_date") AS "plan_date", 
    max("c1"."realisation_date") AS "realisation_date", 
    max("c1"."score") AS "score", 
    max("c2"."plan_date") AS "next_date", 
    max("c2"."id") AS "next_id" 
FROM "controls" AS "c1" 
LEFT JOIN "controls" AS "c2" ON "c1"."next_id" = "c2"."id" 
WHERE "c2"."realisation_date" IS NULL AND "c1"."next_id" IS NOT NULL AND "c1"."realisation_date" <= '2023-07-27' 
GROUP BY c1."measure_id", c1."clause" 
ORDER BY c1."clause" asc;

What do you think? source of aggregate function in pgsql: https://www.postgresql.org/docs/9.5/functions-aggregate.html

[dbarzin]

As string_agg does not exists in mySQL, I propose to test the database drive and submit the corresponding SQL statement to the database.

[yjacolin]

Depending on what you planned to get, you can use max("name") also!

[dbarzin]

I have push another SQL query that works with mySQL.

[yjacolin]

perfect! Thanks Didier.