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.