Re: Embedded SQL: rollup

On 1/26/2016 3:42 PM, Booth Martin wrote:

I have a group of records I want to sum(sales) by day, by week, and by
month. It seems to me that I want to use rollup(), not sum() ? I can't
make it work. So, I am missing something, probably something basic. I
can not find a simple end-to-end example that does this. I am pretty
sure the trouble lays in my not understanding how to retrieve the
7-days, 4 weeks, one month. Thats 7 + 4 + 1 = 11 totals. I can't
figure out how to find them.

select month, week, day, sum(amt)
from sales
group by month, week, day with rollup
order by month, week, day

The key ideas:
Every column in the select clause needs to be in the group by clause
-or- a summary function like sum().
The group by clause is very similar to L3, L2, L1.
This group by will generate a unique row for every combination of
month/week/day.
sum() will accumulate the total down to the level specified in the group
by. Here, month/week/day.
with rollup means that SQL will generate the totals for the 'other'
level breaks. This one does a sum() for the break of month/week/day, so
'with rollup' will generate totals for month/week, month, and final
total (LR).