CODEWITHSUNDEEP
sqlhsqldb
Marcin Wasiluk
Marcin Wasiluk

Reputation: 4884

hsqldb count for each day

having table

id|date|somefield

I need to get count of entries for each day of the year 

select EXTRACT (DAY_OF_YEAR FROM date) as day, id from table

works fine

but when I try 

select EXTRACT (DAY_OF_YEAR FROM date) as day, count(*) from table

fails

select count(*) from table group by EXTRACT (DAY_OF_YEAR FROM date)

fails as well

Upvotes: 1

Views: 235

Answers (1)

Kevin Bowersox
Kevin Bowersox

Reputation: 94489

You need to add a group by expression. Here is some pseudo code, I will work up a SqlFiddle in a moment.

select EXTRACT (DAY_OF_YEAR FROM date) as day, 
count(*) 
from table
group by EXTRACT (DAY_OF_YEAR FROM date)

SQLFIDDLE (Using MYSQL) http://sqlfiddle.com/#!2/42c9e/10

Upvotes: 1

Related Questions