Incorrect results for SQL count with rolled up data

We’ve been running Druid in production for more than a year, but have just recently begun exploring Druid SQL to improve ease of use for our customers, instead of using native querying, which we use internally.

However, it seems the very basic COUNT(*) is not doing a normal:

“aggregations”: [
{
`` “type”: “longSum”,
"fieldName": "count" }
]

``

but rather a:

“aggregations”: [
{
`` “type”: “count”,
"name": "a0" }
]

``

which obviously will not yield the correct result since it’s giving back the number of rolled-up rows and not the total sum of the ingested events.

Instead, I’ve been trying with things like

{
“query”: “SELECT SUM(__count) AS nbEvents FROM xxx”
}

``

and variants of it, but always get Calcite errors.

So what is the correct way of getting the very basic normal longSum count aggregator to work with Druid SQL when using roll-up?

We’re running Druid 0.15

Why do you use “__count” in dsql query? I don’t see your aggregator is defined that way.

If the filed name is count you can put a double quote and try :

eg:

select sum(“count”) from

Thanks,

Vaibhav

Thanks for the reply Vaibhav.

Actually, what you suggested was the first thing I tried, but since I was referring to the http/json api syntax I simply did a late night typo using:

{ "query": "SELECT SUM('count') AS nbEvents FROM xxx" }

``

when it should of course have been:

{ "query": "SELECT SUM(\"count\") AS nbEvents FROM xxx" }

``

So now the dsql result matches the http/json one nicely.

Thanks for your quick reply.

:+1:

Just wanted to plug a video that I made about rollup in Druid: https://www.youtube.com/watch?v=u551R7voe7w that addresses these querying questions also in the second half of the video