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

On 1/5/2017 2:24 PM, Steinmetz, Paul wrote:

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

So... SQL's command line isn't the same thing as a query reporting tool.
It's not intended to give us things like 'list the detail lines and at
the bottom a count of how many there were'. That said, you can wrestle
with SQL to get what you're after.

As David notes, count(*) is the way to get the final total. One way is
to UNION the detail with the total line:

SELECT REMOTE_ADDRESS , JOB_NAME , LOCAL_PORT, '1' as sort, 1 as n
FROM QSYS2.NETSTAT_JOB_INFO
WHERE LOCAL_PORT = 23
AND JOB_TYPE = 'INTERACTIVE'
AND JOB_NAME LIKE '%QPADEV%'
union
select ' ', ' ', 0 , '9', count(*) as n
from qsys2.netstat_job_info
WHERE LOCAL_PORT = 23
AND JOB_TYPE = 'INTERACTIVE'
AND JOB_NAME LIKE '%QPADEV%'
group by 4, 1, 2, 3
order by 4, 1, 2, 3

You could make that a CTE to your final query in order to return only
the 'interesting' columns, but you get the general idea.

Another possibility is ROLLUP:

SELECT REMOTE_ADDRESS , JOB_NAME , LOCAL_PORT, count(*) as n
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 with rollup
order by 1, 2, 3

The rollup doesn't work without the count(*).

Hope this gives you some ideas ti play with.