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:[email protected]] On Behalf Of > >Lindsay Morris > >Sent: Wednesday, July 29, 2009 4:40 PM > >To: [email protected] > >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 <[email protected]> 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:[email protected]] On Behalf > >>Of Huebschman, George J. > >>Sent: Wednesday, July 29, 2009 10:59 AM > >>To: [email protected] > >>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 > >> > >>
