Count objects and group by day

Hi,

I'd like to have a count of the renditions that were created in my system since Monday last week, and group that count by day. I thought this would work:

select count(r_object_id), datefloor(day,set_time) from dmr_content where set_time >= '7-Nov-2011' and rendition = 2 group by datefloor(day,set_time)

But I get a bad attribute name error message. Can anybody help me?

Thanks!

Raph

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

sivarry

Hi Raph,

Try this,

select count(r_object_id), DATETOSTRING(set_time,'dd-mon-yy') as st from dmr_content where set_time >= Date('07/11/11','dd/mm/yy') and rendition = 2 group by DATETOSTRING(set_time,'dd-mon-yy') order by 2

-Siva

All comments

sivarry

Hi Raph,

Try this,

-Siva

mrgou

Thank you Siva, it works. Do you know why DATETOSTRING works but DATEFLOOR doesn't?

sivarry

Hi,

I did not get any error with your query but the results were not as expected. It might be because of the underlying RDBMS support on these functions. Kindly refer DQL Reference manual on more information on functions and their support in different RDBMS

-Siva

mrgou

Thank you very much for your help and advice.