Re: How do I create a SQL SELECT to give me totals on a level break example order number?

Do exactly what I proposed before
1. List all fields in the select line, the column which shoud be
accumultated gets an aggregate function.
2. List all fields in the group by in the grouping sets in the first
parenthesis. If you do not another total, specify a second column:

The following query generates a total for the 18 Columns (=Detail), a
total for Col1 and a Gand Total.
The sequence where your totals appear depends on you ORDER BY

SELECT Col1, Col2, .... Col18, sum(Total)
FROM YourFile
GROUP BY Grouping Sets ( (Col1, Col2, Col3 .... Col18), (Col1), ())
ORDER BY Col1, Colx ....

With query should return what you want! Just try it!

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)



-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] Im Auftrag
von a4g atl
Gesendet: Thursday, 02.2 2023 19:23
An: Midrange Systems Technical Discussion
Betreff: Re: How do I create a SQL SELECT to give me totals on a level
break example order number?

Maybe what I need to do is not possible.
The file I am selecting from has 18 fields. There is 1 key field and I
need to total the numeri fields.

Order, Line, status, part, quantity ordered, quantity received, order value
123 1 O aa1 10 0
10.00
123 2 P bb2 15 0
20.00
total 123
30.00

124 1 O cc1 5 0
50.00
124 2 O dd1 15 0
30.00
total 124
80.00

total
110.00

I need all fields to be displayed not just the keys and group fields.

Can I achieve this or does it need RPG?

Thanks, Darryl


On Thu, Feb 2, 2023 at 11:47 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

Try:

Select PONbr, Line, Vendor, Sum(Cost)
From YourTable
Group By Grouping Sets ((PONbr, Line, Vendor), ()) Order By PONbr,
Line, Vendor

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)


-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] Im
Auftrag von stefan@xxxxxxxxxx
Gesendet: Thursday, 02.2 2023 16:19
An: 'Midrange Systems Technical Discussion'
Betreff: RE: How do I create a SQL SELECT to give me totals on a level
break example order number?

Example:
Select * from lib.file
group by grouping sets (PONUM)

What I would like to see is

PO Number line Vendor Cost
1234 1 abc 10.00
1234 2 abc 12.00
Vendor Total 22.00

1235 1 def 5.00
1235 2 def 12.00
Vendor Total 17.00

Company Total 29.00

I may misunderstand what you are trying to do, but doesn't.

group by rollup (PONUM) give you what you want?

Best regards

Stefan

--
No trees were killed in the sending of this message, but a large
number of electrons were terribly upset.

Stefan Tageson
+46 732 369934
stefan@xxxxxxxxxx


--
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 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.