I have the following PostgreSQL:

select
    A.*,
    B.child,
    REGEXP_MATCHES(A.b_number, '([^.]*--[0-9]*).*') as number,
    sum(cast(A.amount AS decimal)) as sum_amount,
    count(A.amount) as cnt_amount
into result
from B
join A on B.name = A.name and B.parent = A.id
join C on A.name = C.name and B.child = C.id
group by A.name, A.unit, number;

select * from result;

But I get the following error:

SQL Error [42803]: ERROR: column "A.index" must appear in the GROUP BY clause or be used in an aggregate function.

What is the reason for this?

I tried adding A.index to the GROUP BY clause but it only kept asking for different columns. I also tried creating a subquery but failed because I have two joins and I'm trying to create a new table result.

CodePudding user response：

Here is a version with the GROUP BY problem corrected:

SELECT
    A.name,
    A.unit,
    B.child,
    REGEXP_MATCHES(A.b_number, '([^.]*--[0-9]*).*') AS number,
    SUM(CAST(A.amount AS decimal)) AS sum_amount,
    COUNT(A.amount) AS cnt_amount
INTO result
FROM B
INNER JOIN A ON B.name = A.name AND B.parent = A.id
INNER JOIN C ON A.name = C.name AND B.child = C.id
GROUP BY
    A.name,
    A.unit,
    B.child,
    number;

Note that every column/alias which appears in the SELECT clause also appears in GROUP BY. Exceptions to this are columns which appear inside aggregate functions. In that case, it is OK for them to not appear in GROUP BY.