Search code examples
sqloracle-databasedatestamp

Aggregate on relative daterange in Oracle SQL

I have a table with several columns I would like to aggregate on, I have already aggregated on absolute days, but now I want to aggregate relatively. In my table I have a timestamp, and I would like to group by a date range so that the datestamp's day is 0, the day after 1 and so forth. 

SELECT count(*) num_elements, sum(some_value) like_to_sum, reldate 
FROM the_large_table 
GROUP BY *** what goes here *** reldate.

Can I aggregate on the return from a function, or is there a better way to approach this problem.

If I can aggregate on the result from a function, are there any functions for this already in SQL, or how could this be solved?

Solution 
  • SELECT
    COUNT(*) AS num_elements,
    SUM(some_value) AS like_to_sum,
    TRUNC(SYSDATE) - TRUNC(your_date) AS rel_date
FROM the_large_table
GROUP BY TRUNC(SYSDATE) - TRUNC(your_date);