× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Birgitta, thank you so much. I have it working like a charm.

On Thu, Feb 2, 2023 at 1:58 PM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2023 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.