Let's say you have a sales table with the Columns Company, CustNo, SalesDate and Amount and you want to accumulate your sales per year and customer and a column for each month containing the monthly sales.
The query would look like this:
SELECT CustNo, YEAR(SalesDate) as SalesYear,
SUM(CASE WHEN MONTH(SalesDate)= 1 THEN Amount ELSE 0 END) AS JAN,
SUM(CASE WHEN MONTH(SalesDate)= 2 THEN Amount ELSE 0 END) AS FEB,
SUM(CASE WHEN MONTH(SalesDate)= 3 THEN Amount ELSE 0 END) AS MAR,
SUM(CASE WHEN MONTH(SalesDate)= 4 THEN Amount ELSE 0 END) AS APR,
SUM(CASE WHEN MONTH(SalesDate)= 5 THEN Amount ELSE 0 END) AS MAY,
SUM(CASE WHEN MONTH(SalesDate)= 6 THEN Amount ELSE 0 END) AS JUN,
SUM(CASE WHEN MONTH(SalesDate)= 7 THEN Amount ELSE 0 END) AS JUL,
SUM(CASE WHEN MONTH(SalesDate)= 8 THEN Amount ELSE 0 END) AS AUG,
SUM(CASE WHEN MONTH(SalesDate)= 9 THEN Amount ELSE 0 END) AS SEP,
SUM(CASE WHEN MONTH(SalesDate)= 10 THEN Amount ELSE 0 END) AS OCT,
SUM(CASE WHEN MONTH(SalesDate)= 11 THEN Amount ELSE 0 END) AS NOV,
SUM(CASE WHEN MONTH(SalesDate)= 12 THEN Amount ELSE 0 END) AS DEC,
SUM(Amount) AS Total
FROM Sales
GROUP BY CustNo, YEAR(SalesDate)
Order BY SalesYear;
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 23:48
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Don Brown <DBrown@xxxxxxxxxx>
Subject: RE: SQL listagg help
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.
--
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.
--
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.
midrange.com
