How to get column count and break on new column value??

Larry Bernacki

How to get column count and break on new column value??
Hello all,
I have been asked to provide SQL that will provide a count of the
number of rows given a specific value. For instance, if it were a
employee table, list all employees by department, sorted by department,
and given that the output will be grouped by department, provide a count
of the number of employees at each new department.

Thank you in advance,
Larry

---------------------------------------------------------------------------------
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. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

John McKown

Re: How to get column count and break on new column value??
(in response to Larry Bernacki)
Isn't this just:

SELECT DEPTNO, COUNT(EMPNO)
FROM EMPLOYEES
GROUP BY DEPTNO
SORT BY DEPTNO
;

where DEPTNO is the department number and EMPNO is the employee number.
I assume that an employee is only in a single department.

Or am I missing something?



--
John McKown
Senior Systems Programmer
UICI Insurance Center
Information Technology

This message (including any attachments) contains confidential
information intended for a specific individual and purpose, and its'
content is protected by law. If you are not the intended recipient, you
should delete this message and are hereby notified that any disclosure,
copying, or distribution of this transmission, or taking any action
based on it, is strictly prohibited.


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Larry Bernacki
Sent: Friday, December 23, 2005 8:29 AM
To: [login to unmask email]
Subject: [DB2-L] How to get column count and break on new column
value??



Hello all,
I have been asked to provide SQL that will provide a
count of the number of rows given a specific value. For instance, if it
were a employee table, list all employees by department, sorted by
department, and given that the output will be grouped by department,
provide a count of the number of employees at each new department.

Thank you in advance,
Larry
------------------------------------------------------------------------
--------- 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. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
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. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Kumar Vaddadi/EIG

Re: How to get column count and break on new column value??
(in response to John McKown)

I think it should be ORDER BY... not SORT BY. I guess like
SELECT DEPTNO, COUNT(EMPNO)
FROM EMPLOYEES
GROUP BY DEPTNO
ORDER BY DEPTNO

Happy holidays..
************* Thanks & Regards *******************
Vaddadi Shyam Kumar
IBM Db2 Certified Database Administrator
Data Management Group
The Economical Insurance Group, Waterloo.
Reach me @ 519-570-8500 ext 2242
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Happiness is when what you think, what you say, and what you do are in
harmony. ----- Mahatma Gandhi
******************************************************************************************************************************


|---------+---------------------------->
| | "McKown, John" |
| | <[login to unmask email]|
| | INSCTR.COM> |
| | Sent by: DB2 Data|
| | Base Discussion |
| | List |
| | <[login to unmask email]|
| | ORG> |
| | |
| | |
| | 12/23/2005 09:44 |
| | AM |
| | Please respond to|
| | DB2 Database |
| | Discussion list |
| | at IDUG |
| | |
|---------+---------------------------->
>------------------------------------------------------------------------------------------------------------------------------|
| |
| To: [login to unmask email] |
| cc: |
| Subject: Re: [DB2-L] How to get column count and break on new column value?? |
>------------------------------------------------------------------------------------------------------------------------------|




Isn't this just:

SELECT DEPTNO, COUNT(EMPNO)
FROM EMPLOYEES
GROUP BY DEPTNO
SORT BY DEPTNO
;

where DEPTNO is the department number and EMPNO is the employee number. I
assume that an employee is only in a single department.

Or am I missing something?




--
John McKown
Senior Systems Programmer
UICI Insurance Center
Information Technology

This message (including any attachments) contains confidential information
intended for a specific individual and purpose, and its' content is
protected by law. If you are not the intended recipient, you should delete
this message and are hereby notified that any disclosure, copying, or
distribution of this transmission, or taking any action based on it, is
strictly prohibited.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Larry Bernacki
Sent: Friday, December 23, 2005 8:29 AM
To: [login to unmask email]
Subject: [DB2-L] How to get column count and break on new column value??


Hello all,
I have been asked to provide SQL that will provide a count of the
number of rows given a specific value. For instance, if it were a
employee table, list all employees by department, sorted by department,
and given that the output will be grouped by department, provide a count
of the number of employees at each new department.

Thank you in advance,
Larry
---------------------------------------------------------------------------------
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. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
---------------------------------------------------------------------------------
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. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm







***
Confidentiality Warning: This message and any attachments transmitted with it are confidential and intended solely for the use of the individual(s) or entity to whom they are addressed. If you have received this email in error please delete this message and any attachments. If you are not the intended recipient, you are notified that any review, retransmission, conversion to hard copy, copying, circulation or other use of this message and any attachments is strictly prohibited.

Information confidentielle : Le présent message, ainsi que tout fichier qui y est joint, est envoyé à l'intention exclusive de son ou ses destinataires; il est de nature confidentielle. Si vous n'êtes pas le destinataire prévu, supprimez ce message et tout document joint. Nous avertissons toute personne autre que le destinataire prévu que tout examen, transmission, impression, copie, distribution ou autre utilisation de ce message et de tout fichier qui y est joint est strictement interdit.

John McKown

Re: How to get column count and break on new column value??
(in response to Kumar Vaddadi/EIG)
> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email] On Behalf Of Kumar Vaddadi/EIG
> Sent: Friday, December 23, 2005 9:01 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] How to get column count and break on new
> column value??
>
>
>
> I think it should be ORDER BY... not SORT BY. I guess like
> SELECT DEPTNO, COUNT(EMPNO)
> FROM EMPLOYEES
> GROUP BY DEPTNO
> ORDER BY DEPTNO
>
> Happy holidays..
> ************* Thanks & Regards *******************
> Vaddadi Shyam Kumar

Blame it on my cold. You're right. ORDER, not SORT.

--
John McKown
Senior Systems Programmer
UICI Insurance Center
Information Technology

This message (including any attachments) contains confidential
information intended for a specific individual and purpose, and its'
content is protected by law. If you are not the intended recipient, you
should delete this message and are hereby notified that any disclosure,
copying, or distribution of this transmission, or taking any action
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. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Larry Bernacki

Re: How to get column count and break on new column value??
(in response to John McKown)
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 T
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 '2005-10-04'
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 rows (distinct serial
numbers), for the criterial that matches the 'where' clause, somewhere in
the output.

Thank you,
Larry







"McKown, John" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/23/2005 09:44 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] How to get column count and break on new column value??






Isn't this just:

SELECT DEPTNO, COUNT(EMPNO)
FROM EMPLOYEES
GROUP BY DEPTNO
SORT BY DEPTNO
;

where DEPTNO is the department number and EMPNO is the employee number. I
assume that an employee is only in a single department.

Or am I missing something?


--
John McKown
Senior Systems Programmer
UICI Insurance Center
Information Technology

This message (including any attachments) contains confidential information
intended for a specific individual and purpose, and its' content is
protected by law. If you are not the intended recipient, you should
delete this message and are hereby notified that any disclosure, copying,
or distribution of this transmission, or taking any action based on it, is
strictly prohibited.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Larry Bernacki
Sent: Friday, December 23, 2005 8:29 AM
To: [login to unmask email]
Subject: [DB2-L] How to get column count and break on new column value??


Hello all,
I have been asked to provide SQL that will provide a count of the
number of rows given a specific value. For instance, if it were a
employee table, list all employees by department, sorted by department,
and given that the output will be grouped by department, provide a count
of the number of employees at each new department.

Thank you in advance,
Larry
---------------------------------------------------------------------------------
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. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
---------------------------------------------------------------------------------
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. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
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. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Chris Pomasl

Re: How to get column count and break on new column value??
(in response to Larry Bernacki)
On Fri December 23 2005 8:21 am, Larry Bernacki so notably scribed:
> 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 T
> 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 '2005-10-04'
> 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 rows (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 to output a count
of the previous set of data. For this you need a program or a report writer
(IBM's QMF or CA's PRF on MVS).

Once you get into the use of aggregating functions (count), 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 GROUP BY, then all
rows are aggregated to one and ALL selected columns must have an aggregating
function.

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
Senior Software Engineer, Computer Associates
Always remember, you are unique...just like everyone else.
IBM Certified, DB2 Z/OS V7 Administration, User

---------------------------------------------------------------------------------
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. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Charles Greer

Re: How to get column count and break on new column value??
(in response to John McKown)
I've done this kind of thing before by joining to the T
table twice, but the SQL gets really ugly. A subselect
is rather cleaner.

> SELECT AP.DEVICE_ID, AP.DATE_MSMT,

(select count(t1.ser_no) from usmasps.tblapprovaldata t1
where AP.MODE_S = T1.HEX_MODE_S
AND T1.OPERATOR = 'XXX'
AND AP.DEVICE_ID = 'WWWW'
AND T1.TYPE = 'B752'),

> T.OPERATOR, T.SER_NO, T.REGNO, AP.ASE,
> AP.ASE_SD, AP.ASE_OBS_NUM
> FROM RVSM.AC_PERFORMANCE AP, USMASPS.TBLAPPROVALDATA T
> 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 '2005-10-04'
> ORDER BY T.SER_NO, T.REGNO, AP.DATE_MSMT
> ;



Charles Greer
Software Systems Analyst
County of Sonoma, Geographic Information Systems
2615 Paulin Drive
Santa Rosa, CA 95403
707-565-3991 FAX 707-565-2187
[login to unmask email]

---------------------------------------------------------------------------------
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. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

John McKown

Re: How to get column count and break on new column value??
(in response to Chris Pomasl)
> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email] On Behalf Of Christopher J Pomasl
> 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 new
> column value??
>
>
> On Fri December 23 2005 8:21 am, Larry Bernacki so notably scribed:
> > 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 T
> > 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 '2005-10-04'
> > 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 rows
> (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 to
> output a count
> of the previous set of data. For this you need a program or
> a report writer
> (IBM's QMF or CA's PRF on MVS).
>
> Once you get into the use of aggregating functions (count),
> 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 GROUP
> BY, then all
> rows are aggregated to one and ALL selected columns must have
> an aggregating
> function.
>
> 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 language.

However, just to be weird (and I'm "on" cold medicine right now), would
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 EMPNO
is not a number, then the second SQL statement would need to convert
COUNT(EMPNO) to character.

--
John McKown
Senior Systems Programmer
UICI Insurance Center
Information Technology

This message (including any attachments) contains confidential
information intended for a specific individual and purpose, and its'
content is protected by law. If you are not the intended recipient, you
should delete this message and are hereby notified that any disclosure,
copying, or distribution of this transmission, or taking any action
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. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

[login to unmask email]

Re: How to get column count and break on new column value??
(in response to Charles Greer)
All,

> It can't be done with SQL alone.
>
> There is no concept in SQL for breaking the stream of rows to output a
count
> of the previous set of data. For this you need a program or a report
writer
> (IBM's QMF or CA's PRF on MVS).

That said, ... how about something like this:

SELECT AP.DATE_MSMT,
T.SER_NO, 0 AS TOTAL_COUNT , T.REGNO, AP.ASE,
AP.ASE_SD, AP.ASE_OBS_NUM, '1' AS SORT_ORDER
FROM RVSM.AC_PERFORMANCE AP, USMASPS.TBLAPPROVALDATA T
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 '2005-10-04'
UNION ALL
SELECT ' ',
T.SER_NO, COUNT(*) AS TOTAL_COUNT, ' ', ' ',
' ', ' ', '2' AS SORT_ORDER
FROM RVSM.AC_PERFORMANCE AP, USMASPS.TBLAPPROVALDATA T
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 '2005-10-04'
GROUP BY T.SER_NO
ORDER BY SORT_ORDER, T.SER_NO, T.REGNO, AP.DATE_MSMT


The concept of adding a "Sort Column" is not a new one. I've modified it
(and the original query) slightly here just to make the point. The output
result of the full query is a set of rows; the ones ending in '1' are the
"detail" rows, the '2's are the "total" rows. Of course, I make no claim
that this produces exactly what is wanted, or that it will perform well.

Larry, maybe this will give you some ideas. Hope this helps!

Lock Lyon
Compuware Corp




Christopher J Pomasl <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/23/2005 11:56 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] How to get column count and break on new column value??






On Fri December 23 2005 8:21 am, Larry Bernacki so notably scribed:
> 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.
>
[...snip...]
>
> 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 rows (distinct
serial
> numbers), for the criterial that matches the 'where' clause, somewhere
in
> the output.
It can't be done with SQL alone.

[...snip...]

--
Christopher J Pomasl
Senior Software Engineer, Computer Associates
Always remember, you are unique...just like everyone else.
IBM Certified, DB2 Z/OS V7 Administration, User

---------------------------------------------------------------------------------
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. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Larry Bernacki

Re: How to get column count and break on new column value??
(in response to LL581@DAIMLERCHRYSLER.COM)
Thank you all who have responded. We came up with the following to get
the results we were looking for.. I will take the other suggestions
and test each one of them.

SELECT AP.DEVICE_ID, AP.DATE_MSMT,
T.OPERATOR, T.SER_NO, T.REGNO, AP.ASE,
AP.ASE_SD, AP.ASE_OBS_NUM, MYCOUNT.USECOUNT
FROM RVSM.AC_PERFORMANCE AP, USMASPS.TBLAPPROVALDATA T
JOIN
(SELECT AX.MODE_S, COUNT(*) AS USECOUNT
FROM RVSM.AC_PERFORMANCE AX, USMASPS.TBLAPPROVALDATA TX
WHERE AX.MODE_S = TX.HEX_MODE_S
AND TX.OPERATOR = 'UAL'
AND AX.DEVICE_ID = 'AICT'
AND TX.TYPE = 'B752'
AND AX.DATE_MSMT BETWEEN '2005-04-08' AND '2005-10-04'
GROUP BY AX.MODE_S) MYCOUNT
ON
T.HEX_MODE_S = MYCOUNT.MODE_S
WHERE AP.MODE_S = T.HEX_MODE_S
AND T.OPERATOR = 'UAL'
AND AP.DEVICE_ID = 'AICT'
AND T.TYPE = 'B752'
AND AP.DATE_MSMT BETWEEN '2005-04-08' AND '2005-10-04'
ORDER BY T.SER_NO, T.REGNO, AP.DATE_MSMT
;

---------------------------------------------------------------------------------
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. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Chris Pomasl

Re: How to get column count and break on new column value??
(in response to Larry Bernacki)
On Fri December 23 2005 10:56 am, [login to unmask email] so notably
scribed:
> All,
>
> > It can't be done with SQL alone.
> >
> > There is no concept in SQL for breaking the stream of rows to output a
>
> count
>
> > of the previous set of data. For this you need a program or a report
>
> writer
>
> > (IBM's QMF or CA's PRF on MVS).
>
> That said, ... how about something like this:
>
> SELECT AP.DATE_MSMT,
> T.SER_NO, 0 AS TOTAL_COUNT , T.REGNO, AP.ASE,
> AP.ASE_SD, AP.ASE_OBS_NUM, '1' AS SORT_ORDER
> FROM RVSM.AC_PERFORMANCE AP, USMASPS.TBLAPPROVALDATA T
> 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 '2005-10-04'
> UNION ALL
> SELECT ' ',
> T.SER_NO, COUNT(*) AS TOTAL_COUNT, ' ', ' ',
> ' ', ' ', '2' AS SORT_ORDER
> FROM RVSM.AC_PERFORMANCE AP, USMASPS.TBLAPPROVALDATA T
> 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 '2005-10-04'
> GROUP BY T.SER_NO
> ORDER BY SORT_ORDER, T.SER_NO, T.REGNO, AP.DATE_MSMT
>
>
Hey! Cool.
I tend to avoid UNIONs (since PRF doesn't generate them). I also think of
GROUP as acting on the fullselect rather than a subselect (since I avoid
UNIONs). My bad!!

This solution certainly does what is asked.
Except for one thing.

Change the ORDER BY to:

ORDER BY T.SER_NO, SORT_ORDER, T.REGNO, AP.DATE_MSMT

Since the OP wanted a break when the SER_NO changes, you want it to be on the
outside of the sort. Your sort order would put all detail rows at the top
followed by all of the count rows. Sort order being second would put details
for a "group" first then the count row. Then the details for the next group.

Also note, the query will go through the data twice (unless the optimizer
recognizes the similarity between the subselects) thus adding to the time and
resource usage involved.

--
Christopher J Pomasl
Senior Software Engineer, Computer Associates
Always remember, you are unique...just like everyone else.
IBM Certified, DB2 Z/OS V7 Administration, User

---------------------------------------------------------------------------------
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. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm