Wanda, Actually it "works", but doesn't "work" when I plug it into the statement shown in the post to which I responded, I get results (not an error), but they are definitely goofy.
I'll report back if I find anything more substantive on this subject, but I suspect that it may not be possible to do (at least easily) what is being asked. Regards, Andy Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] The only dumb question is the one that goes unasked. The command line is your friend. "Good enough" is the enemy of excellence. "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]> wrote on 12/01/2004 10:39:49: > Andy, > > I don't think this is possible. > > DISTINCT doesn't help > TSM always responds "unknown SQL column" when a results column is > included in the group by > It won't take a function in the GROUP BY > And It won't let me do a SELECT inside the FROM > > > > -----Original Message----- > From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of > Andrew Raibeck > Sent: Wednesday, December 01, 2004 10:37 AM > To: [EMAIL PROTECTED] > Subject: Re: SQL Help > > > Try adding the DISTINCT keyword between SELECT and DATE(PENDING_DATE), > i.e. > > select distinct date(pending_date) ... > > You'll want to verify that the results are correct by matching up the > counts against the results of the select without DISTINCT. For example, > using the output you show below, I'd expect to see a single line showing > a > count of 5 for 11/27/2004. > > Regards, > > Andy > > Andy Raibeck > IBM Software Group > Tivoli Storage Manager Client Development > Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] > Internet e-mail: [EMAIL PROTECTED] > > The only dumb question is the one that goes unasked. > The command line is your friend. > "Good enough" is the enemy of excellence. > > "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]> wrote on 12/01/2004 > 08:18:16: > > > That does not give me the desired results: > > > > tsm: ULTSM> select date(pending_date) as "Date",count(*) from volumes > > where - > > cont> status='PENDING' group by pending_date > > > > Date Unnamed[2] > > ---------- ----------- > > 2004-11-27 1 > > 2004-11-27 1 > > 2004-11-27 1 > > 2004-11-27 1 > > 2004-11-27 1 > > > > group by pending_date groups by the time stamp not the date stamp. I > > want a count of pending tapes by date not by second. > > > > >>> [EMAIL PROTECTED] 12/1/2004 10:02:40 AM >>> > > The group by statement is incorrect, use the following: > > > > group by pending_date > > > > the conversion to date format is already taken care of > > in the first part of the select statement. > > > > David E Ehresman wrote: > > > > > tsm: ULTSM> select date(pending_date) as "Date",count(*) from > > volumes > > > where - > > > cont> status='PENDING' group by "Date" > > > ANR2940E The reference 'Date' is an unknown SQL column name. > > > > > > | > > > > > > .........................................................V..... > > > e",count(*) from volumes where status='PENDING' group by > > > "Date" > > > > > > ANS8001I Return code 3. > > > > > > > > > > > >>>>[EMAIL PROTECTED] 11/30/2004 9:09:03 AM >>> > > > > > > From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On > > > Behalf Of David E Ehresman > > > > > >>I'm trying to get a count of tapes pending by date. I'm using: > > >> select date(pending_date),count(*) from volumes where > > >>status='PENDING' - > > >> group by date(pending_date) > > >>but I get the error message: > > >> ANR2904E Unexpected SQL key word token - 'DATE'. > > >>pointing to the date in the group by clause. > > >> > > >>Anyone know how to get a pending count by date? > > > > > > > > > Try > > > > > > select date(pending_date) as "Date",count(*) from volumes where > > > status='PENDING' group by "Date" > > > > > > -- > > > Mark Stapleton ([EMAIL PROTECTED]) > > > Berbee Information Networks > > > Office 262.521.5627