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).

--
--buck

Visit wiki.midrange.com and register for an account. Edit a page that
helps you, and because it's public, you'll help someone else, too!

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.