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
