TopN and GroupBy queries in SQL?

How can I convert topN and groupBy queries to SQL for querying in druid?

I tried using curl with

{

“query”:“SELECT TopN(bytes) AS TotalBytes FROM “requests” WHERE “code”=‘403’ limit 1000”

}

but the response is just a single value rather than the top 1000.

Is my syntax wrong ?

Hey Naveen,

You need to group on something to get the top N of that thing. Let’s say you want the top “srcaddr” by total numbers of bytes. The query would be:

SELECT srcaddr, SUM(bytes) AS TotalBytes FROM “requests” WHERE “code” = ‘403’ GROUP BY srcaddr ORDER BY SUM(bytes) DESC LIMIT 1000

HI Naveen,

TopN is used by default for queries that group by a single expression, do have ORDER BY and LIMIT clauses, do not have HAVING clauses, and are not nested.

You should be able to run

{

“query”:“SELECT bytes AS TotalBytes from requests where code=403 group by 1 order by bytes DESC limit 1000”

}

Does this give you a different result?

Eric Graham

Solutions Engineer -** **Imply

**cell: **303-589-4581

email: eric.graham@imply.io

www.imply.io

“query”:“SELECT repo,SUM(bytes) AS TotalBytes FROM “requests” WHERE “code”=‘403’ GROUP BY repo order by SUM(bytes) DESC limit 1000”

This query returns a single value

[{“TotalBytes”:2030}]

Thanks!

Hey Eric,

This returns a single value

[{“TotalBytes”:2030}]

Thanks!

Can you try

{

“query”:“SELECT bytes,code AS TotalBytes from requests where code=403 group by 1,2 order by code DESC limit 1000”

}

How many rows does this show?

Eric Graham

Solutions Engineer -** **Imply

**cell: **303-589-4581

email: eric.graham@imply.io

www.imply.io

{

“query”:“SELECT bytes,code from requests where code=403 group by 1,2 order by code DESC limit 1000”

}

Eric Graham

Solutions Engineer -** **Imply

**cell: **303-589-4581

email: eric.graham@imply.io

www.imply.io