CODEWITHSUNDEEP
sqlpostgresql
chrise
chrise

Reputation: 63

postgres string_agg and GROUP BY clause

I can't see why the query below should need any group by? I can't get this to work whereas other uses of string_agg elsewhere work

Any suggestions appreciated

select dv.DocumentVersionID, dv.DocumentID, dv.AuthorUserID1, dv.AuthorUserID2, dv.AuthorUserID3,
(select string_agg(
     ((COALESCE ((', ' || author1.FirstName || ' ' || author1.LastName), ' ')) ||
     (COALESCE ((', ' || author2.FirstName || ' ' || author2.LastName), ' ')) ||
     (COALESCE ((', ' || author3.FirstName || ' ' || author3.LastName), ' ')))  , ' ' ))   as Authors
FROM  DocumentVersion AS dv  INNER JOIN
     "User" AS author1 ON author1.UserID = dv.AuthorUserID1 LEFT OUTER JOIN
     "User" AS author2 ON author2.UserID = dv.AuthorUserID2 LEFT OUTER JOIN
     "User" AS author3 ON author3.UserID = dv.AuthorUserID3  ;

results in:-

ERROR:  column "dv.documentversionid" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select dv.DocumentVersionID, dv.DocumentID, dv.AuthorUserID1...

Upvotes: 1

Views: 1722

Answers (2)

lmarqs
lmarqs

Reputation: 1471

All fields that are not into a aggregation function must be putted on GROUP BY clause. I just could not understand why you are using string_agg. A simple concatenation should work in your case.

SELECT 
 dv.documentversionid, 
 dv.documentid, 
 dv.authoruserid1, 
 dv.authoruserid2, 
 dv.authoruserid3, 
 COALESCE(author1.firstname||' '||author1.lastname,'') 
   ||COALESCE(', '||author2.firstname|| ' '||author2.lastname,'') 
   ||COALESCE(', '||author3.firstname||' '|| author3.lastname,'') 
 AS Authors 
FROM 
 documentversion AS dv 
 INNER JOIN "user" AS author1 ON author1.userid = dv.authoruserid1
 LEFT OUTER JOIN "user" AS author2 ON author2.userid = authoruserid2 
 LEFT OUTER JOIN "user" AS author3 ON author3.userid = authoruserid3

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

because the select does not have a from clause so the references are all to the outer query. You have an aggregation function, so they are being aggregated. You have unaggregated columns as well, so you need a group by.

You need neither the subquery nor string_agg():

SELECT dv.DocumentVersionID, dv.DocumentID, dv.AuthorUserID1, dv.AuthorUserID2, dv.AuthorUserID3,
       (COALESCE(', ' || author1.FirstName || ' ' || author1.LastName, ' ') ||
        COALESCE(', ' || author2.FirstName || ' ' || author2.LastName, ' ') ||
        COALESCE(', ' || author3.FirstName || ' ' || author3.LastName, ' ')
       )   as Authors
FROM DocumentVersion dv  INNER JOIN
     "User" author1
     ON author1.UserID = dv.AuthorUserID1 LEFT OUTER JOIN
     "User" author2
     ON author2.UserID = dv.AuthorUserID2 LEFT OUTER JOIN
     "User" author3
     ON author3.UserID = dv.AuthorUserID3  ;

And if you want to remove the leading ,:

SELECT dv.DocumentVersionID, dv.DocumentID, dv.AuthorUserID1, dv.AuthorUserID2, dv.AuthorUserID3,
       substr(COALESCE(', ' || author1.FirstName || ' ' || author1.LastName, ' ') ||
              COALESCE(', ' || author2.FirstName || ' ' || author2.LastName, ' ') ||
              COALESCE(', ' || author3.FirstName || ' ' || author3.LastName, ' '), 3
             )   as Authors
FROM DocumentVersion dv  INNER JOIN
     "User" author1
     ON author1.UserID = dv.AuthorUserID1 LEFT OUTER JOIN
     "User" author2
     ON author2.UserID = dv.AuthorUserID2 LEFT OUTER JOIN
     "User" author3
     ON author3.UserID = dv.AuthorUserID3  ;

Upvotes: 2

Related Questions