You mean something like this:
1. Assumed I have the following view with sales per year and customer:
Salesyear CustNo Description Amount
2008 10001 Fruits, Vegetables & Co
115,00
2008 10002 Herrmann & Bauer GmbH 1350,00
2008 10003 Goldbach GmbH
535,00
2008 10004 The Company
470,00
2008 10005 Alzenauer Dönertreff
310,00
2009 10001 Fruits, Vegetables & Co
2634,20
2009 10002 Herrmann & Bauer GmbH 1636,25
2009 10003 Goldbach GmbH
4589,86
2009 10004 The Company
2673,95
2009 10005 Alzenauer Dönertreff
3741,95
2010 10001 Fruits, Vegetables & Co
281,94
2010 10003 Goldbach GmbH
1555,75
The following SQL-Statement will produce the following result:
Select CustNo, SalesYear, Amount,
Sum(Amount) Over(Partition By CustNo Order By CustNo, SalesYear)
RunTotal,
Sum(Amount) Over(Order By CustNo, SalesYear) RunAll
from SalesCusty
Order By CustNo, SalesYear
CustNo SalesYear Amount RunTotal RunAll
10001 2008 115,00 115,00 115,00
10001 2009 2634,20 2749,20 2749,20
10001 2010 281,94 3031,14 3031,14
10002 2008 1350,00 1350,00 4381,14
10002 2009 1636,25 2986,25 6017,39
10003 2008 535,00 535,00 6552,39
10003 2009 4589,86 5124,86 11142,25
10003 2010 1555,75 6680,61 12698,00
10004 2008 470,00 470,00 13168,00
10004 2009 2673,95 3143,95 15841,95
10005 2008 310,00 310,00 16151,95
10005 2009 3741,95 4051,95 19893,90
In the RUNALL column is a running total over all rows.
In the RUNTOTAL column the running total restart with the next customer
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Don
Brown
Sent: Mittwoch, 13. Dezember 2017 04:07
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: SQL Question running totals
Rob that is really neat and I have added to my examples database.
But not what I was after - I was looking for the sub totals to be on the
same line like a bank statement.
So you would have;
Amount Running total
And not sure I could achieve that with grouping sets ?
Thanks
Don Brown
From: "Rob Berendt" <rob@xxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date: 12/12/2017 11:13 PM
Subject: Re: SQL Question running totals
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
That's really old.
Replace all // with double dashes. I changed them to // because email
readers often truncate after double dashes. It's how you chop off that
signature your work may force you to have.
// category: My Samples
// description: Subtotals - With Grouping sets // // Sample with "Detail",
subtotal, grand total // The trick is the detail is really a subtotal for
each row // If you don't want detail, omit
// Any columns selected which are not part of any grouping set, or
aggregate (like sum)
// The grouping set below flagged as "Detail"
//
// For information on output_queue_entries check out //
http://ibm.biz/DB2foriServices // SELECT
OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME, SPOOLED_FILE_NAME,
sum(SIZE) as size, sum(TOTAL_PAGES) as pages, JOB_NAME,
CREATE_TIMESTAMP
FROM QSYS2.OUTPUT_QUEUE_ENTRIES
group by grouping sets (
(OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME, SPOOLED_FILE_NAME,
JOB_NAME, CREATE_TIMESTAMP) // "Detail"
,(OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME) // subtotal
,() // Grand Total
)
order by OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME, SPOOLED_FILE_NAME,
JOB_NAME, CREATE_TIMESTAMP;
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Luis Rodriguez <luisro58@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 12/12/2017 07:54 AM
Subject: Re: SQL Question running totals
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Don,
Hectic week and all that... Check the following ITJungle's articles for an
easy method to achieve running totals:
https://www.itjungle.com/2011/03/02/fhg030211-story01/
https://www.itjungle.com/2011/07/13/fhg071311-story01/
HTH,
Luis
Luis Rodriguez
midrange.com
