Tag Archives: must appear in the GROUP BY clause or be used in an aggregate function

[Solved] must appear in the GROUP BY clause or be used in an aggregate function

Leave a reply

Today, when I use Postgres database to write sql, I found an error after execution: column “XXXXXX” must appear in the GROUP BY clause or be used in an aggregate function. After putting the query fields into groups according to the prompts, the results of the query are not what I need. Yes, I checked the solution on the Internet, hereby record

The cause of the problem: This is a common aggregation problem in pgsql. Before the SQL3 standard, the field selected for display must appear in  GROUP BY the

Assuming that there is a makerar table, the data is as follows:

 cname  | wmname |          avg           
--------+-------------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  | 1.00000000000000000000
 spain  | usopp  |     5.0000000000000000

I would like tocname grouping query to avg value of the largest data, sql as follows

SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

However, error

ERROR:  column "makerar.wmname" must appear in the GROUP BY clause or be used in an   aggregate function 
LINE 1: SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

So, I putAfter the group that wmname joined

SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname, wmname;

The result is as follows, but this is not the data I want

cname  | wmname |          max           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  | 1.00000000000000000000
 spain  | usopp  |     5.0000000000000000

My desired data result is

 cname  | wmname |          max           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

There are two ways to solve this problem:

1. Using nested sql, compute the aggregate in a subquery, then join it with itself to get the additional columns that need to be displayed

SELECT m.cname, m.wmname, t.mx
FROM (
    SELECT cname, MAX(avg) AS mx
    FROM makerar
    GROUP BY cname
    ) t JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg
;

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

2. Use the special DISTINCT ON expression

SELECT DISTINCT ON (cname) 
    cname, wmname, avg
FROM 
    makerar
ORDER BY 
    cname, avg DESC ;

Combined with your own business, use the first method to transform sql

    SELECT
        pihs.pat_status,
        b.bed_class_id,
        pih.charge_class_id,
        pihs.pat_condition,
        pih.pat_id,
        pihs.room_with_mother_baby_flag,
        pc.pat_in_charge_doc_name,
        pc.pat_in_charge_doc_id,
        b.bed_show_no,
        pih.pat_age AS age,
        pih.pat_in_hos_id,
        pih.pat_in_hos_code,
        pihs.pat_in_status,
        pc.state_of_critical_value,
        pih.pat_in_time,
        pc.chief_doc_id,
        pc.chief_doc_name,
        pc.dur_nurse_id,
        pc.dur_nurse_name,
        pc.first_ann_bed_time,
        pc.manage_bed_nurse_name,
        pih.pat_type_id,
        pc.pat_clinic_id,
        pc.pat_ward_id,
        b.bed_id,
        b.bed_status,
        b.is_share,
        pc.nursing_class,
        pc.nursing_class_name,
        pihs.pat_in_bed_share_status,
        pih.seven_days_again_in_flag,
        b.price,
        T.change_in_time
        FROM
        bed b
        LEFT JOIN pat_in_hospital_status pihs ON b.bed_id = pihs.bed_id
        LEFT JOIN pat_in_hospital pih ON b.pat_in_hos_id = pih.pat_in_hos_id
        LEFT JOIN pat_clinican pc ON pih.pat_in_hos_id = pc.pat_in_hos_id
        LEFT JOIN ( SELECT pat_in_hos_id, MAX ( pat_in_out_ward_time ) AS change_in_time FROM change_ward_record WHERE complete_flag = '1' GROUP BY pat_in_hos_id ) T ON pih.pat_in_hos_id = T.pat_in_hos_id
        where b.ward_id='1234'

Reference link:

https://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function#

[Solved] must appear in the GROUP BY clause or be used in an aggregate function

Today, during the grouping statistics, PgSQL reported errors most appear in the group by clause or be used in an aggregate function , which is OK in mysql, but PgSQL reported errors. I went to stackoverflow to query and found that someone had encountered the same problem as me. This is a common aggregation problem in PgSQL. Before the SQL3 standard, PgSQL reported errors, The fields selected for display must appear in group by . Let me describe the problem as follows:

There is a table called makerar , in which the records are as follows: CNAME | wmname | AVG
– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

I want to query the maximum AVG of each CNAME , which is written as mysql

SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;

Report error in PgSQL

ERROR:  column "makerar.wmname" must appear in the GROUP BY clause or be used in an aggregate function 
LINE 1: SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

This means that the wmname field must appear in group by or be used for aggregate functions

So I added the wmname field after group by according to the error prompt

SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname, wmname;

The result is

cname wmname avg
canada zoro 2.00
spain luffy 1.00
spain usopp 5.00

And what I expect is

cname wmname avg
canada zoro 2.00
spain usopp 5.00

There are two solutions, but I only understand one, so I recorded this one

The general idea is to complete the aggregation in the subquery, and then associate the table containing the fields you want to display (in this case, makerar itself) to get the fields (in this case, wmname), so the SQL will look like this

SELECT   
  t.cname,
  m.wmname,
  t.max
FROM 
  (SELECT 
    cname,
    MAX(avg) AS max 
  FROM makerar
  GROUP BY cname) t
LEFT JOIN makerar m ON t.cname = m.cname AND t.max = m.avg;

https://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function#