Re: [druid-user] "Group by" and Count(*) show 0 / zero values as well

What you are asking for isn’t available in SQL in any database that I have worked with. In the past, I have solved this using some subquery and LEFT JOIN type of shenanigans. But I haven’t tried it yet with Druid. I would love it if someone corrects me though! I don’t think the native query behaves any different.

https://try2explore.com/questions/11933890

An additional suggestion is you can use sum of case when event matches then 1 else 0. Sometimes joins could be an expensive operation.
Regards,
Kundan

Hi Lax,

Let’s say that in your table UserEvents you have user records with different events, but you only want to count eventType=1, then
Your query would be like this:

Select name, sum(case when eventType=1 then 1 else 0 end) as eventCount
From UserEvents
Group By 1
Order By 2 DESC

  • The users with no records at all in UserEvents will obviously not appear in this result, if you have some other table from where you can get the list of all users you will have to join it to get all users in the final result.

Hi Kundan -

I don’t have an example, but Kundan gave a simple one already. The idea is, return 1 for a row when there’s an event, 0 when there’s not, and take the sum.
For a more general case than Kundan’s example, you could use something like this:

SELECT name, SUM(CASE WHEN eventType IS NULL THEN 0 ELSE 1) AS eventCount
FROM userEvents
GROUP BY 1
ORDER BY 2 DESC

I didn’t test that, but I think it should work.