RE: SQL using GROUP BY with ROLLUP

If the Column is Character ... yes! ... But I'd suggest to have an extra
column with column textes
Grouping(ColumnName) will Return 1 if a NULL value is in the column and 0 if
there is no column.
Then you simply use a case clause:

Select Case When Grouping(SalesYear) = 1 Then 'Grand Total'
When Grouping(CustNo) = 1 Then 'Total Year ' concat SalesYear

Else ''
End as Summary_Text,
SalesYear, CustNo, Sum(Amount)
From x
Group By RollUp(SalesYear, CustNo)
Order By SalesYear, CustNo;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
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 a4g
atl
Sent: Tuesday, 11 June 2024 17:58
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: SQL using GROUP BY with ROLLUP

I am using a SQL statement to display records with totals.
Is there a way to replace the NULL value on the summarised lines with some
text?

Example:

Part Date Qty
abc 06/01/2024 10
abc - 10
- - 10

Select Part, Date, sum(qty) from lib.table group by Part, Date with rollup
order by Part, Date

With a bit more complex of a report, the NULLS make it difficult to see the
total lines. I would like to replace the NULL with a text description like
TOTAL.

Can this be done?

TIA, Darryl Freinkel.
--
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.