Weekly/monthly/quarterly grouping in query

Question

Lets say I have table with following columns

1. Client - string.
2. Profit - integer.
3. Deal_Date - date.

I need query that will retrieve sum of profit breakdown by week/month/quater etc.

Expected output for weeks

1 row, sum (profit) of all deals that registered from (03.19.2012 - 03.12.2012).
2 row, sum (profit) of all deals that registered from (03.12.2012 - 03.05.2012).
...
n row, sum (profit) of all deals that registered from (05.17.2011 - 05.10.2011).

NOTE (dates set just for example)

The same for month, years, etc.

Could someone help me with such query?

Btw performance is very important.

Could you show the output you want? What date range do you need to represent? When you say "performance is very important" what are your criteria? How much data are you talking about? — APC, Mar 19 '12 at 11:23
possible duplicate of [SQL group by frequency within a date range](http://stackoverflow.com/questions/8640138/sql-group-by-frequency-within-a-date-range) — Andriy M, Mar 19 '12 at 15:21

score 4 · Accepted Answer · answered Mar 19 '12 at 11:37

This query uses simple date formats to extract the various elements you want to track and analytics to get the sums.

select client
       , yr
       , qtr
       , wk
       , sum ( profit ) over ( partition by client, yr) as yr_profit
       , sum ( profit ) over ( partition by client, yr, qtr) as qtr_profit
       , sum ( profit ) over ( partition by client, yr, wk) as wk_profit
from ( 
         select client
                , profit
                , to_char(deal_date, 'yyyy') as yr
                , to_char(deal_date, 'q') as qt
                , to_char(deal_date, 'ww') as wk
          from your_table )
/

This will produce one row for each row in the current table. So you probebly will want to wrap it in a further outer query which only returns only distinct rows.

A variant would be to use rollup instead. I'm not sure how well that works when the grouping criteria aren't perfectly hierarchical (weeks don't fit neatly into quarters).

select client
       , yr
       , qtr
       , wk
       , sum ( profit ) as profit
from ( 
         select client
                , profit
                , to_char(deal_date, 'yyyy') as yr
                , to_char(deal_date, 'q') as qt
                , to_char(deal_date, 'ww') as wk
          from your_table )
group by rollup ( client, yr, qtr, wk )
/

score -2 · Answer 2 · answered Mar 19 '12 at 11:50

-2

Just make an SP and loop the code for each week or month or year as you wish.

answered Mar 19 '12 at 11:50

Bhabani Sankar Mishra

403
2
6
21

2

This would have been a bad answer in 1997 but Row By Agonising Row is unacceptable now. Oracle has really moved on in the last fifteen years and has lots of different, better performing options. – APC Mar 19 '12 at 12:54

Weekly/monthly/quarterly grouping in query

2 Answers2