Thanks Birgitta,
I was looking for a better way to do this.
select coy as "Company",
ac as "Account",
sl as "Sub-Ledger",
(select ifnull(sum(amt),0) as "July" from dtrpf t2 where t2.coy
= t1.coy and t2.ac = t1.ac and t2.per = 13 and typ = 'INV'),
(select ifnull(sum(amt),0) as "August" from dtrpf t3 where t3.coy
= t1.coy and t3.ac = t1.ac and t3.per = 14 and typ = 'INV'),
(select ifnull(sum(amt),0) as "Total" from dtrpf t4 where t4.coy =
t1.coy and t4.ac = t1.ac and t4.per between 13 and 14 and typ = 'INV')
from dtrpf t1
where coy = 1 and sl = 2 and between 13 and 14 and typ = 'INV'
group by coy, ac, sl
order by "Total" desc
fetch first 100 rows only;
The above is only doing 2 months plus a total and is hard coded, The
requirement would actually be for 12 months plus a total.
I thought listagg would enable me to dynamically have the number of months
found in the where clause ?
So a sample of the data would be like
Coy Ac SL Per Amt
001 A1 02 13 10.00
001 A1 02 13 10.00
001 A1 02 13 10.00
001 A1 02 13 10.00
001 A1 02 14 10.00
001 A1 02 14 10.00
001 A1 02 14 10.00
001 A1 02 15 10.00
001 A1 02 15 10.00
And the output I was wanting is like
Coy Ac SL Per13 Per14 Per15
001 A1 02 40.00 30.00 20.00
Is that possible with listagg or if not is it possible with another option
?
Thank you
Don
From: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxxxxxxxx>
Date: 06/09/2023 08:53 PM
Subject: RE: SQL listagg help
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>
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
