Value Widget with ClickHouse Query

[sharpSteff]

I have a query which creates a time series:

SELECT
        interval,
        COUNT(*) as countValue
    FROM (
        SELECT 
            toStartOfInterval(timestamp, toIntervalMinute(1)) AS interval,
            t1.tagMap['service.instance.id'] as instance,
            t1.tagMap['service.version'] AS version
        FROM signoz_traces.signoz_index_v2 t1
        WHERE t1.serviceName == 'myservice'
        AND t1.name == 'mytrace'
        AND t1.hasError == true
        AND t1.timestamp BETWEEN {{.start_datetime}} AND {{.end_datetime}}
    ) AS subquery 
    GROUP BY
        interval
    ORDER BY interval ASC

I like to have a Value-Widget which sums all the values in the timeframe via ClickHouse Query:
However I do not find any docs what query the Value-Widget expects:

I tried following:

SELECT SUM(countValue) as value 
FROM (
    SELECT
        interval,
        COUNT(*) as countValue
    FROM (
        SELECT 
            toStartOfInterval(timestamp, toIntervalMinute(1)) AS interval,
            t1.tagMap['service.instance.id'] as instance,
            t1.tagMap['service.version'] AS version
        FROM signoz_traces.signoz_index_v2 t1
        WHERE t1.serviceName == 'myservice'
        AND t1.name == 'mytrace'
        AND t1.hasError == true
        AND t1.timestamp BETWEEN {{.start_datetime}} AND {{.end_datetime}}
    ) AS subquery 
    GROUP BY
        interval
    ORDER BY interval ASC
)

However its always "0" :(

[srikanthccv]

Can you try adding a dummy timestamp to query like now() as ts and check again?

SELECT SUM(countValue) as value
now() as ts
FROM (
    SELECT
        interval,
        COUNT(*) as countValue
    FROM (
        SELECT 
            toStartOfInterval(timestamp, toIntervalMinute(1)) AS interval,
            t1.tagMap['service.instance.id'] as instance,
            t1.tagMap['service.version'] AS version
        FROM signoz_traces.signoz_index_v2 t1
        WHERE t1.serviceName == 'myservice'
        AND t1.name == 'mytrace'
        AND t1.hasError == true
        AND t1.timestamp BETWEEN {{.start_datetime}} AND {{.end_datetime}}
    ) AS subquery 
    GROUP BY
        interval
    ORDER BY interval ASC
)