Functions are not allowed in GROUP BY clause, only column names :(

On 30/07, Steven Harris wrote:
> Hi George
>
> I have a copy of the interesting bits of a TSM database in sqlite3 for a
> side project I'm doing while "resting".  On that database I can use
>
> select count(*), cast(abs(pct_utilized-.01)/10 as integer) as decile,
> stgpool_name
> from volumes
> where devclass_name != 'DISK'
> group by stgpool_name, cast(abs(pct_utilized-.01)/10 as integer)
>
> to give something like what you are looking for.  The main issue is that
> deciles with no members are not listed.  You may need to adjust the SQL
> for the TSM database engine, but I can't see anything that I know isn't
> valid TSM sql.
>
> Regards
>
> Steve
>
> Steven Harris
> TSM Admin, "resting" in Sydney Australia
>
> Huebschman, George J. wrote:
> >No, just the command line
> >
> >-----Original Message-----
> >From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf 
> >Of
> >Lindsay Morris
> >Sent: Wednesday, July 29, 2009 4:40 PM
> >To: ADSM-L AT VM.MARIST DOT EDU
> >Subject: Re: [ADSM-L] Summarizing Tape Utilization
> >
> >George, aren't you a Servergraph user? Doesn't it give you what you
> >need?   I don't know what you're trying to accomplish here, but
> >Servergraph handles the usual culprits...
> >
> >--------------------
> >Lindsay Morris
> >Principal
> >TSMworks, Inc.
> >1-919-403-8260
> >www.tsmworks.com
> >
> >On Jul 29, 2009, at 2:52 PM, Bob Levad <blevad AT WINNEBAGOIND DOT COM> 
> >wrote:
> >
> >
> >>I think you'll need a separate query for each utilization range.
> >>At least, I haven't thought of a good way to iterate.
> >>
> >>
> >>
> >>select stgpool_name, count(*) as "60% < utilized < 70%" - from volumes
> >>
> >
> >
> >>- where devclass_name='LTOCLASS4' -
> >>  and pct_utilized>=60 -
> >>  and pct_utilized<70 -
> >>group by stgpool_name -
> >>order by stgpool_name
> >>
> >>Etc...
> >>
> >>
> >>You could maybe nest several of these selects inside another select,
> >>but that can be pretty cumbersome.
> >>
> >>Bob
> >>
> >>
> >>
> >>
> >>
> >>-----Original Message-----
> >>From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf
> >>Of Huebschman, George J.
> >>Sent: Wednesday, July 29, 2009 10:59 AM
> >>To: ADSM-L AT VM.MARIST DOT EDU
> >>Subject: [ADSM-L] Summarizing Tape Utilization
> >>
> >>Greetings everyone,
> >>I have a/an SQL Select question.
> >>
> >>Most of my TSM Servers are at 5.5.1.0, one is at 5.5.2.0
> >>
> >>I am trying to count the number of tapes with a percentage utilization
> >>
> >
> >
> >>in brackets of 10 percent.  In other words, how many tapes with
> >>utilization between 100 and 90, 90 and 80, and so forth.
> >>
> >>I first tried:
> >>select count(volume_name), pct_utilized, stgpool_name from volumes -
> >>where (pct_utilized between 100 and 90) or (pct_utilized between 90
> >>and
> >>80) or (pct_utilized between 70 and 60) or (pct_utilized between 60
> >>and
> >>50) or (pct_utilized>50)-
> >>group by stgpool_name, pct_utilized
> >>
> >>Although the statement functions and returns valid data,  it is not
> >>what I expected.  I mistakenly expected it to count all the tapes
> >>within each given range.  What it really does is count tapes with
> >>distinct pct_util.
> >>I might as well not have specified the ranges.  The only tapes it
> >>counted cumulatively were of the exact same pct_util.
> >>
> >>Unnamed[1]     PCT_UTILIZED     STGPOOL_NAME
> >>-----------     ------------     ------------------
> >>         1             59.4     C_TSMSERVER_TAPE
> >>         1             68.7     C_TSMSERVER_TAPE
> >>         1             78.2     C_TSMSERVER_TAPE
> >>         1             79.5     C_TSMSERVER_TAPE
> >>         1             99.9     C_TSMSERVER_TAPE
> >>         1            100.0     C_TSMSERVER_TAPE
> >>         1             66.0     NASPOOL
> >>
> >>If I select for a count for greater than or less than a particular
> >>value, I get the kind of count I expect.  I had expected "between" to
> >>do similar work but be less klunky.
> >>
> >>select count(volume_name) from volumes where pct_utilized<100 and
> >>pct_utilized>89
> >>
> >>
> >>The other option I tried was CASE, WHEN, THEN:
> >>
> >>select count(case when pct_utilized between 100 and 91 then 1 else
> >>0) from
> >>volumes and, select count(case when (pct_utilized between 100 and
> >>91) then 1
> >>else 0) from volumes
> >>
> >>Those failed for syntax errors.
> >>
> >>Is there a clean way to do this?
> >>
> >>George Huebschman
> >>
> >>