What about:
Select Col1, Col2, Col3, Count(*)
From YourFile
Group By RollUp (Col1, Col2, Col3);
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)
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Steinmetz, Paul
Sent: Donnerstag, 5. Januar 2017 21:20
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxx>
Subject: RE: SQL function to return the grand total number of elements found
Rob,
I had those results, but could not get a grand total.
Found this in SQL reference manual.
grand-total
Both CUBE and ROLLUP return a row which is the overall (grand total)
aggregation. This may be separately specified with empty parentheses within
the GROUPING SETS clause. It may also be specified directly in the GROUP BY
clause, although there is no effect on the result of the query. Example C4
uses the grand-total syntax.
Example C4:
If you run the same query as Example C3 only replace ROLLUP with CUBE, you
can see additional grouping sets for (WEEK, SALES_PERSON), (DAY_WEEK,
SALES_PERSON), (DAY_WEEK), and (SALES_PERSON) in the result.
SELECT WEEK(SALES_DATE) AS WEEK,
DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
SALES_PERSON,
SUM(SALES) AS UNITS_SOLD
FROM SALES
WHERE WEEK(SALES_DATE) = 13
GROUP BY CUBE( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON ) ORDER
BY WEEK, DAY_WEEK, SALES_PERSON
Still no luck.
Paul
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob
Berendt
Sent: Thursday, January 05, 2017 2:37 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL function to return the grand total number of elements found
Your explanation doesn't jive with the statement.
Can you give a sample?
From what I see you should be seeing
10.10.1.1 JOB1001 23
10.10.1.1 JOB1001 37
10.10.1.1 JOB1002 23
10.10.1.1 JOB1002 37
10.10.1.2 JOB1001 23
10.10.1.2 JOB1001 37
...
If you want to see a count you'll have to add it.
10.10.1.1 JOB1001 23 1
10.10.1.1 JOB1001 37 1
10.10.1.1 JOB1002 23 1
10.10.1.1 JOB1002 37 1
10.10.1.2 JOB1001 23 1
10.10.1.2 JOB1001 37 1
...
And they'll always be one since you are grouping it by such a finite detail
and I cannot imagine the same remote IP address, same job, and same remote
port having more than one.
Proper utilization of those functions mentioned may result in:
10.10.1.1 JOB1001 23 1
10.10.1.1 JOB1001 37 1
10.10.1.1 JOB1001 2
10.10.1.1 JOB1002 23 1
10.10.1.1 JOB1002 37 1
10.10.1.1 JOB1002 2
10.10.1.1 4
10.10.1.2 JOB1001 23 1
10.10.1.2 JOB1001 37 1
10.10.1.2 JOB1001 2
10.10.1.2 2
10.
...
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: "Steinmetz, Paul" <PSteinmetz@xxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 01/05/2017 02:24 PM
Subject: RE: SQL function to return the grand total number of
elements found
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Rob,
Yes, that is what I'm seeing.
I have a list of 3 columns, would like to see just one grand total showing
total number of items.
I tried CUBE and ROLLUP, results in sub-breaks.
What do I need to add just a grand total number of items.
SELECT REMOTE_ADDRESS , JOB_NAME , LOCAL_PORT FROM QSYS2.NETSTAT_JOB_INFO
WHERE LOCAL_PORT = 23 AND JOB_TYPE = 'INTERACTIVE' AND JOB_NAME LIKE
'%QPADEV%' GROUP BY REMOTE_ADDRESS, JOB_NAME , LOCAL_PORT ORDER BY
REMOTE_ADDRESS, JOB_NAME , LOCAL_PORT SELECT statement run complete.
Paul
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob
Berendt
Sent: Thursday, January 05, 2017 2:09 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL function to return the grand total number of elements found
Count(*) will return a grand total, providing that is the only column you
selected.
select count(*)
from itemmaster;
However, if you do something like this:
select itemNumber, count(*)
from itemmaster
Then you are going to get each row.
There is a way to get subtotals.
Select ItemClass, count(*)
from itemmaster
Group By ItemClass;
There are also ways to get multiple levels of control breaks using things
like CUBE and ROLLUP.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: "Steinmetz, Paul" <PSteinmetz@xxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 01/05/2017 02:01 PM
Subject: SQL function to return the grand total number of elements
found
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
What SQL function should be used to return the grand total number of
elements found.
I tried count, but that returned a count of each element, not a grand
total count.
Thank You
_____
Paul Steinmetz
IBM i Systems Administrator
Pencor Services, Inc.
462 Delaware Ave
Palmerton Pa 18071
610-826-9117 work
610-826-9188 fax
610-349-0913 cell
610-377-6012 home
psteinmetz@xxxxxxxxxx
http://www.pencor.com/
midrange.com
