RE: SQL function to return the grand total number of elements found

Finally got it.
I'm sure there's other options.
Combination of COUNT(*) and ROLLUP.

SELECT REMOTE_ADDRESS , JOB_NAME , LOCAL_PORT, Count(*) FROM
QSYS2.NETSTAT_JOB_INFO WHERE LOCAL_PORT = 23 AND JOB_TYPE =
'INTERACTIVE' AND JOB_NAME LIKE '%QPADEV%' GROUP BY
ROLLUP(REMOTE_ADDRESS,JOB_NAME,LOCAL_PORT) ORDER BY REMOTE_ADDRESS
, JOB_NAME

192.168.80.77 915649/PPALKUPI/QPADEV008N 23 1
192.168.80.77 915649/PPALKUPI/QPADEV008N - 1
192.168.80.77 - - 1
- - - 152
* * * E N D O F R E P O R T * * *

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike Jones
Sent: Thursday, January 05, 2017 4:07 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL function to return the grand total number of elements found

GROUP BY GROUPING SETS allows you to specify the exact desired levels of sub-totals and/or grand-totals that you desire. I highly recommend getting proficient with it since it provides more flexibility than CUBE and ROLLUP.

CUBE and ROLLUP are great too, but they don't provide the level of flexibility that GROUP BY GROUPING SETS provides. CUBE and ROLLUP are the better choice if you want all the totals they are designed to provide.
GROUP BY GROUPING SETS is the better choice when you don't want all the totals.

Mike

date: Thu, 5 Jan 2017 20:20:29 +0000
from: "Steinmetz, Paul" <PSteinmetz@xxxxxxxxxx>
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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD