> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email] On Behalf Of Christopher J
> Sent: Friday, December 23, 2005 10:56 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] How to get column count and break on
> column value??
> On Fri December 23 2005 8:21 am, Larry Bernacki so notably
> > John,
> > Thanks for the response. Here is what I am really
> trying to get
> > done. Here is the real SQL that the user is running.
> > SELECT AP.DEVICE_ID, AP.DATE_MSMT, COUNT(T.SER_NO),
> > T.OPERATOR, T.SER_NO, T.REGNO, AP.ASE,
> > AP.ASE_SD, AP.ASE_OBS_NUM
> > FROM RVSM.AC_PERFORMANCE AP, USMASPS.TBLAPPROVALDATA
> > WHERE AP.MODE_S = T.HEX_MODE_S
> > AND T.OPERATOR = 'XXX'
> > AND AP.DEVICE_ID = 'WWWW'
> > AND T.TYPE = 'B752'
> > AND AP.DATE_MSMT BETWEEN '2005-04-08' AND
> > ORDER BY T.SER_NO, T.REGNO, AP.DATE_MSMT
> > ;
> > They want a break and count on each new serial number
> (T.SER_NO) either
> > as a total at the end of each group, or if need be a
> cumulative count in
> > an additional column. They want the total number of
> (distinct serial
> > numbers), for the criterial that matches the 'where'
> clause, somewhere in
> > the output.
> It can't be done with SQL alone.
> There is no concept in SQL for breaking the stream of rows
> output a count
> of the previous set of data. For this you need a program
> a report writer
> (IBM's QMF or CA's PRF on MVS).
> Once you get into the use of aggregating functions
> all selected
> columns outside of the GROUP BY must then also have an
> aggregating function
> applied to them (SUM, AVG, COUNT, etc). If you have no
> BY, then all
> rows are aggregated to one and ALL selected columns must
> an aggregating
> If you need all of the detail and then an aggregation at a
> break point, you
> need something outside of SQL to aggregate for you.
> Christopher J Pomasl
I agree. This really needs to be done in a host programming
However, just to be weird (and I'm "on" cold medicine right now),
something like this work?
CREATE TEMPORARY TABLE WAGA AS
SELECT "DETAIL" AS TYPE, DEPTNO, EMPNO
INSERT INTO WAGA (TYPE, DEPTNO, EMPNO)
SELECT "SUMMARY", DEPTNO, COUNT(EMPNO)
GROUP BY DEPTNO
SELECT TYPE, DEPTNO, EMPNO
ORDER BY DEPTNO, TYPE
DROP TABLE WAGA
I ASSuME this is perhaps a one-shot or low-use requirement. Oh, if
is not a number, then the second SQL statement would need to
COUNT(EMPNO) to character.
Senior Systems Programmer
UICI Insurance Center
This message (including any attachments) contains confidential
information intended for a specific individual and purpose, and
content is protected by law. If you are not the intended recipient,
should delete this message and are hereby notified that any
copying, or distribution of this transmission, or taking any
based on it, is strictly prohibited.
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ
is at http://www.idugdb2-l.org.
List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm