Re: Embedded SQL: rollup

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Is it an actual DATE data type?

You still want to use SUM()

something like
select
  itemnbr, invdate,
  month(invdate), week(invdate), sum(sales)

from
  mytbl
group by itemnbr, rollup by (month(invdate)
                 , week(invdate)
                 , invdate
                )


Gets a bit more difficult if you need to go across years.

If your date isn't a date, then hopfully you have idate to convert it.

Charles


On Tue, Jan 26, 2016 at 3:42 PM, Booth Martin <booth@xxxxxxxxxxxx> 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.
> --
> 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.