PostgreSQL: Aggregate by more than one column

Question

I have the following data format:

id       eventtime           use
A    2015-01-01 00:00:00-01    5.2
.
.    2015-06-06 23:59:50-01    5.7  
B 
.
.

I'm trying get an output of the form:

id        eventtime              use
A         2015-01-01             1200
.         2015-06-06             1400  
.
B         2015-01-01             1500    

basically sum of the item use for every user for each day in my database.

I tried

select id, eventtime, sum(use) from table group by id,eventtime

I got an aggregate in this case that I can't really make sense of i.e that it doesn't add up to anything that it should. Any help would be appreciated, thanks in advance!

Answer 1

You need to convert eventtime to a date by removing the time component. I think the simplest way is using date_trunc():

select id, date_trunc('day', eventtime) as eventday, sum(use)
from table
group by id, eventday
order by id, eventday;

I'm not sure why your sample query has id in the select, but user in the group by.

Answer 2

You want to GROUP BY extract('day' FROM eventtime) or GROUP BY CAST(eventtime AS date).

Note that this will prevent the use of any index on eventtime. If you need that you can create an expression index matching what you use in the group by clause.