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/IBM@IBMUS
Internet e-mail: storman AT us.ibm DOT com

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" <ADSM-L AT VM.MARIST DOT EDU> 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:ADSM-L AT VM.MARIST DOT EDU] On Behalf 
> Of
> Andrew Raibeck
> Sent: Wednesday, December 01, 2004 10:37 AM
> To: ADSM-L AT VM.MARIST DOT EDU
> 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/IBM@IBMUS
> Internet e-mail: storman AT us.ibm DOT com
>
> 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" <ADSM-L AT VM.MARIST DOT EDU> 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.
> >
> > >>> gretchen AT PRINCETON DOT EDU 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.
> > >
> > >
> > >
> > >>>>mark.stapleton AT BERBEE DOT COM 11/30/2004 9:09:03 AM >>>
> > >
> > > From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] 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 (stapleton AT berbee DOT com)
> > > Berbee Information Networks
> > > Office 262.521.5627