CODEWITHSUNDEEP
hql
ShiraP
ShiraP

Reputation: 65

Sum on Count in hql - error Not yet supported place for UDAF 'count'

I'm new here so please be gentle, my first question after using this website for a long time regards the below:

I'm trying to create a sum of count of events in the past 30 days:

select  key, sum((COALESCE(count(*),0)))
from table
Where date>= '2016-08-13'
And date<= '2016-09-11'
group by key;

but the sum doesn't seem to work. i'm looking at the last 30 days, and i would like to count any row that exists for each key, and then sum the counts (i need to count on a daily basis and then sum all day's count).

If you can offer any other way to deal with this issue i'm open for suggestions!

Many thanks, Shira

Upvotes: 0

Views: 12151

Answers (2)

Krypton
Krypton

Reputation: 114

It looks like there was a couple things wrong with your code. I've written this for you, haven't tested it but it passes the syntax test.

SELECT COUNT(key) AS Counting FROM tblname 
WHERE date>= '2016-08-13'
AND date<= '2016-09-11' 
GROUP BY key;

And this might help you. You should definitely be using COUNT for this query.

I'm not sure if it's related but there might be an issue with calling a field 'key' I kept receiving syntax errors for it.

Hope I was able to help!

-Krypton

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

You can't nest aggregate functions in HQL (or SQL). However, if you just want a count of records falling within range for each key, then you can simply just use COUNT(*):

select  key, count(*)
from table
where date >= '2016-08-13' and
      date <= '2016-09-11'
group by key;

Upvotes: 2

Related Questions