HQL count() with group by not returning zero/0 records

Question

String query = "select hour(la.dateLastUpdated) as hour," 
+ "coalesce(count(la), 0) from LoginActivity la" 
+ "where la.dateLastUpdated > :date" 
+ "group by hour(la.dateLastUpdated)" 
+ "order by hour(la.dateLastUpdated)";

Date date = new Date(System.currentTimeMillis() - 12*60*60*1000));

Result I'm getting is like

Hour  Count
----  -----
12    1
13    3
15    4
17    11

But I want result like

Hour  Count
----  -----
12    1
13    3
14    0
15    4
16    0
17    11

That means the zero counts. Tried coalesce but it's not working. Any probable hql query to get expected result? Native query also will do.

*I'm using PostgreSql database

Answer 1

Try this one :::

"Select hour(la.dateLastUpdated) as hour,  count(coalesce(la, 0)) 
from LoginActivity la  
where la.dateLastUpdated > :date 
group by hour(la.dateLastUpdated)  
order by hour(la.dateLastUpdated);"

Answer 2

If the record you want (for example Hour = 14) does not exist in your LoginActivity table, how can you expect it to show up in your resultset?

I assume that you want to list every hour of the day and get record counts based on that; if this is the case then

1. You need a dictionary-like structure that includes every hour of the day to begin with,
2. You need to execute a left outer join from this structure to your resultset, joining on the field Hour.

Answer 3

From postgres you can get the result using this query.

SELECT * FROM generate_series(12,24) AS s(hourdigit)
LEFT JOIN (
    SELECT 
    hour(la.dateLastUpdated) AS hour,
    coalesce(count(la), 0) 
    FROM LoginActivity la
    WHERE la.dateLastUpdated > '2014-05-05'
    GROUP BY hour(la.dateLastUpdated)
    ORDER BY hour(la.dateLastUpdated)
) AS resultdata ON resultdata.hour = s.hourdigit