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