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;