Appear in the Group By Clause or use in an aggregate function

Question

Calculate the first actual bought item and populate the first_actual_item column in tr2_invoice.

SELECT cust_id, total_amount, items, MIN(time_in) 
FROM tr_invoice WHERE total_amount <> 0 
GROUP BY cust_id;

ERROR: column "tr_invoice.total_amount" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT cust_id, total_amount, items, MIN...

I used AVG(), MIN(), MAX(), or ARRAY_AGG() as aggregations for total_amount and items, it would output differently from what I queried on MySQL. Any better solution to solve this?

The error is clear. In SQL, the language, you can't have a column in SELECT that doesn't appear in the `GROUP BY` clause or inside an aggregate function. A single customer may have 1000 invoices with different totals. When you group by `cust_id` which of those 1000 totals should the server return? The smallest? Largest? A random one? — Panagiotis Kanavos, Jun 30 '22 at 09:26
Yes, I know that MySQL has the extremely dubious feature of actually returning an essentially random value in this case. This is a semi-supported feature that exists simply because MySQL didn't really care about SQL compatibility before the later 5.x versions. And MySQL wasn't used in enterprise applications before that, just web sites that rarely had to aggregate objects. That semi-supported feature caused some serious performance regressions in one of the 5.7.x *minor* versions, precisely because it's not officially supported — Panagiotis Kanavos, Jun 30 '22 at 09:32
Already asked [hundreds of times](https://stackoverflow.com/search?q=[postgresql]+must+appear+in+the+GROUP+BY+clause) — , Jun 30 '22 at 09:46
"*it would output differently from what I queried on MySQL.*" - well, MySQL essentially returned random results. So yes, the output in Postgres will be different, as those will not be random. If you want an answer other than "apply an aggregate" you will have to **[edit]** your question and provide some sample input data and the expected output based on that - [formatted text](https://meta.stackoverflow.com/a/251362) please. See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking tables (no screen shots please!) — , Jun 30 '22 at 09:48

score 0 · Answer 1 · answered Jun 30 '22 at 10:11

the selected fields must appear in the GROUP BY clause.

1.

SELECT cust_id, total_amount, items, MIN(time_in) over( PARTITION by cust_id) as min_time_in FROM tr_invoice WHERE total_amount <> 0 ;

2.

SELECT
    b.cust_id     ,
    b.total_amount,
    b.items       ,
    a.min_time_in
from
    (
        SELECT
            cust_id,
            MIN(time_in) as min_time_in
        FROM
            tr_invoice 
        WHERE
            total_amount <> 0
        GROUP BY
            cust_id
    )a
    join
        tr_invoice b
        ON
            a.cust_id=b.cust_id;

Please refer link

Appear in the Group By Clause or use in an aggregate function

1 Answers1