CODEWITHSUNDEEP
sqlpostgresqlgroup-byhaving
Drachenfels
Drachenfels

Reputation: 3286

Combining usage of string_agg and having in postgres

My query:

select
    s.id,
    s.title,
    string_agg(t1.title, ',') as a,
    string_agg(t2.title, ',') as b,
    string_agg(t3.title, ',') as c,
    string_agg(t4.title, ',') as d
from show as s
    left join tag as t1 on t1.show_id = s.id and t1.type='a'
    left join tag as t2 on t2.show_id = s.id and t2.type='b'
    left join tag as t3 on t3.show_id = s.id and t3.type='c'
    left join tag as t4 on t4.show_id = s.id and t4.type='d'
group by
    s.id,
    s.title
having
    t1.title = 'Something';

Throws error:

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

What I do not understand is that string_agg is aggregate function, according to this man page:

http://www.postgresql.org/docs/9.4/static/functions-aggregate.html

My data:

show:
  id: 1
  title: 'test'

tag:
  id: 1
  show_id: 1
  type: 'a'
  title: 'title a'

tag:
  id: 2
  show_id: 1
  type: 'b'
  title: 'title a'

tag:
  id: 3
  show_id: 1
  type: 'c'
  title: 'title c'

tag:
  id: 4
  show_id: 1
  type: 'd'
  title: 'title d'

Upvotes: 1

Views: 1772

Answers (1)

James
James

Reputation: 119

The having command is trying to search on an aggregated field.

Either search by s.title. or group by t1.title.

having s.title = 'Something';

OR can change having to to search on concat field.

having string_agg(t1.title, ',') = 'Something';

Upvotes: 2

Related Questions