Do you want a list of the monthly totals or do you want separate columns.
If a list is enough you can do it with a common Table Expression which first
accumulates the sums and then in the Final-Select you build a list with the
totals (per year or customer or whatever).
If you need separate columns, you cannot use LISTAGG. You could accumulate
and build the columns based on a case clause, i.e. Sum(Case When SalesMonth
= '01' Then Amount Else 0 End),
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
IBM Champion since 2020
"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)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Don
Brown via MIDRANGE-L
Sent: Wednesday, 6 September 2023 11:36
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Don Brown <DBrown@xxxxxxxxxx>
Subject: SQL listagg help
I am trying to use the listagg function and seem to be missing something.
I have a transaction table with the following details I want to include
Company
Account
Period (Month number)
Amount - Need to sum the transaction amount
What I want is a list like
Company Account Total_Month_1 Total_Month_2 Total_Month_3 Etcetera
Total_All_Months
Does anyone have a example of something like this.
I know I have seen one and I was sure it was on this list but not finding in
the archives.
Thanks
Don
--
This email has been scanned for computer viruses. Although MSD has taken
reasonable precautions to ensure no viruses are present in this email, MSD
cannot accept responsibility for any loss or damage arising from the use of
this email or attachments..
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
