psql 9.1 ERROR: column must appear in the GROUP BY clause or be used in an aggregate function

Question

I have 3 tables : summaries, segment, people.

 people

      Column      |            Type             |                     
------------------+-----------------------------+
 id               | bigint                      |
 last_name        | character varying           | 
 middle_name      | character varying           | 
 first_name       | character varying           | 
 current_position | character varying           | 

 summaries

     Column     |            Type             |                       
----------------+-----------------------------+
 id             | bigint                      | 
 channel        | character varying           | 
 show           | character varying           | 
 seg_ids        | character varying           | segment ids
 date           | timestamp without time zone | 
 start_time     | timestamp without time zone | 
 end_time       | timestamp without time zone | 

segments

       Column       |            Type             |                                     
--------------------+-----------------------------+
 id                 | bigint                      | 
 block_id           | integer                     | 
 person_id          | bigint                      | foreign key (FK)
 person_role        | character varying           | 
 summ               | integer                     | summary id FK
 deleted            | boolean                     |

My table summaries has a column seg_ids (segments ids) that is a string of integer that I were able to convert into an array of integer with this query which return over a thousand ids:

select regexp_split_to_array((select rtrim(ltrim(replace((select string_agg(seg_ids, ', ')), '], [', ', '), '['),']') from summaries where date between '2018-07-04' and '2018-07-06'),',')::int[];

Now having that array of integer (seg_ids) I want to use it to show all the summaries and people connected to the segments. I tried unsuccessfully this query:

SELECT summ, block_id, person, seg.id as segid, su.channel, su.show, date::timestamp::date as shdate, "time"(su.start_time) as shst, CONCAT (ppl.last_name, ', ', ppl.first_name) AS full_name, substr(person_role, 1, 2) as person_role 
FROM segments seg    
LEFT JOIN summaries su on seg.summ = su.id    
LEFT JOIN people ppl  on ppl.id = person_id    
HAVING seg.id::int = any (  
   regexp_split_to_array((    
      SELECT   
         rtrim(ltrim(  
             replace(   
                string_agg(seg_ids, ', ')   
             , '], [', ', ')  
        , '['),']')    
      FROM summaries    
      WHERE date between '2018-07-04' and '2018-07-06')   
   ,',')::int[])  order by shdate, channel, shst, show, su.id, block_id, person,seg.id asc;

which is giving me this error:

ERROR:  column "seg.summ" must appear in the GROUP BY clause or be used in an aggregate function

I don't want to group by the result as I would give me less row than expected. How can I rewrite the query so I won't have to group by the result?

update
person is in the segments table.

SELECT summ,
   block_id,
   person,
   seg.id as segid, 
   su.channel, su.show,
   date::TIMESTAMP::date AS shdate,
   "time"(su.start_time) AS shst,
   "time"(su.end_time) AS shet,
   regexp_split_to_array(rtrim(ltrim(su.seg_ids, '['), ']'), ',')::int[] AS seg_id_int_arr,
   CONCAT (ppl.last_name, ', ', ppl.first_name) AS full_name,
   substr(person_role, 1, 2) AS person_role
FROM summaries su
LEFT JOIN segments seg ON seg.summ = su.id
LEFT JOIN people ppl ON ppl.id = person_id
WHERE date BETWEEN '2018-07-04' AND '2018-07-06'
 AND seg.id::int = ANY (regexp_split_to_array(rtrim(ltrim(seg_ids, '['), ']'), ',')::int[])
ORDER BY shdate, channel, shst, show, su.id, block_id, person, seg.id ASC;

@VIPER thanks, the query is not throwing error but does not give the expected result. — mamesaye, Oct 07 '18 at 09:44
You need to post more clearly of what you have and what is expected post the table structure maybe then i can help — Shubham Srivastava, Oct 07 '18 at 10:04
You are using HAVING without a GROUP BY. Perhaps you intended to use WHERE instead? — de1, Oct 07 '18 at 18:23

score 1 · Accepted Answer · answered Oct 08 '18 at 01:18

You are doing some basic things wrong

You should know when to use having/where clause. Where clause is used for filtering rows before an aggregation has been performed(This can be using group by or other aggregation functions). Whereas Having clause is used to filter data after aggregation has been performed. You can refer to this explanation
I see that segment table is already having id to summaries and person id, so the last condition you are trying to achieve in having clause can be simplified with joins. From what I understand you want all segment information where summary lies in a specific date range this could be done by simply improving our join condition like
```
FROM segments seg    
JOIN summaries su on seg.summ = su.id  
AND su.date between '2018-07-04' and '2018-07-0`6'`
LEFT JOIN people ppl on ppl.id = person_id
```
Also I see you have selected a column named person but i cant see it in any of your table

psql 9.1 ERROR: column must appear in the GROUP BY clause or be used in an aggregate function

1 Answers1