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
