Skip to content
Advertisement

How to do group_concat with Integer columns?

This query displays performance of 3 endpoints combined (decided using some priority) over a series of days in the period of last 2 weeks.

select
    date,
    GROUP_CONCAT(endpoint, ', ') as endpoints, -- This works fine, since endpoint is string
    GROUP_CONCAT(success, ', ') as endpoints_successes, -- This fails
    (sum(success) / sum(attempts) * 100) as percentage
from
    some_table
where 
    and datadate = from_timestamp(date_sub(now(), 14), 'yyyyMMdd')
    and endpoint in ( 
        select distinct endpoint 
        from some_table
        order by some_priority desc 
        limit 2
    )
group by date
order by date

Just for display in the dashboard, I need to combine the values of the dataset using something like group_concat. This seems to be working fine for String type column.

However when I want to combine the values of the success field for display it fails with the following error:

No matching function with signature: group_concat(BIGINT, STRING)

How should I be transforming the Int into String for use within group_concat, or is there another way altogether to achieve the same?

Advertisement

Answer

Try casting those numeric values to STRING first before rolling them up using GROUP_CONCAT:

SELECT
    date,
    GROUP_CONCAT(endpoint, ', ') AS endpoints,
    GROUP_CONCAT(CAST(success AS STRING), ', ') AS endpoints_successes,  -- cast here
    SUM(success) / SUM(attempts) * 100 AS percentage
FROM some_table
WHERE
    datadate = FROM_TIMESTAMP(DATE_SUB(NOW(), 14), 'yyyyMMdd') AND
    endpoint IN (SELECT endpoint FROM some_table ORDER BY some_priority DESC LIMIT 2)
GROUP BY date
ORDER BY date;
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement