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

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/