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#
