Re: STRING_AGG and GROUP BY - Mailing list pgsql-general

I've come up with the following query, wonder if you meant something similar -

http://sqlfiddle.com/#!17/4ef8b/48

WITH cte1 AS (

SELECT 

    mid,

    STRING_AGG(x->>'letter', '') AS tiles

FROM (

        SELECT 

            mid,

            JSONB_ARRAY_ELEMENTS(m.tiles) AS x

        FROM moves m

        WHERE m.gid = 1

) AS z

GROUP BY mid),

cte2 AS (

        SELECT 

        mid,

        STRING_AGG(y, ', ') AS words

    FROM (

        SELECT 

            mid,

            FORMAT('%s (%s)', s.word, s.score) AS y

        FROM scores s

        WHERE s.gid = 1

) AS z

GROUP BY mid)

SELECT mid, tiles, words 

FROM cte1 JOIN cte2 using (mid) ORDER BY mid ASC;