CODEWITHSUNDEEP
sqloracle-databasedatetimeaverageaggregate-functions
SQL Novice
SQL Novice

Reputation: 323

Aggregate Records by Month

I have a table which I am querying as shown below. The table has a record for each day. I want to update the query so I can have the data broken down each month (Desired Output). Any ideas on how this can be done? Database is Oracle.

select SNAP_TIME, SEG_GB, DB_SIZE, FREE_GB from dbsize
where SNAP_TIME > sysdate -730

enter image description here

Desired Output

enter image description here

Upvotes: 0

Views: 92

Answers (1)

GMB
GMB

Reputation: 222412

You would typically use trunc() and aggregate functions. Say you want the average of each value for each month, then:

select trunc(snap_time, 'month') snap_month, 
    avg(seg_gb) seg_gb, avg(db_size) db_size, avg(free_gb) free_gb
from dbsize
where snap_time > add_months(sysdate, -12)
group by trunc(snap_time, 'month')
order by snap_month

If you want to actually format the truncated date as mon-yy, as shown in your expected result, use to_char() instead:

select to_char(snap_time, 'mon-yy') snap_month, 
    avg(seg_gb) seg_gb, avg(db_size) db_size, avg(free_gb) free_gb
from dbsize
where snap_time > add_months(sysdate, -12)
group by to_char(snap_time, 'mon-yy')
order by min(snap_time)

Upvotes: 3

Related Questions