Hi Rudy, I am using Sybase 7.0. What does Iif mean? Does your statement count the number of clients or the balances. I want to count the number of similar clients in that range.
Thanks Rudy ----- Original Message ----- From: "rudy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, May 18, 2002 10:29 PM Subject: Re: [CFTALKTor] Looking for a better way > > Is there a way to include the three balance ranges > > in a more complex statement? > > hi mike > > sure > > you did not say which database, so i get to choose the syntax > > i choose microsoft access for this one > > select clientID > , sum(Iif(Balance<200,1,0)) as under200 > , sum(Iif(Balance>200,Iif(Balance<500,1,0),0)) as 200to500 > , sum(Iif(Balance>500,Iif(Balance<1000,1,0),0)) as 500to1000 > , sum(Iif(Balance>1000,1,0)) as over1000 > from clientbalances > group by clientID > > this gives a count of the number of balances within each range for each > individual client > > if you have a different database, use CASE inside the SUMs > > yes, you have to modify the query if you add another range > > if you want to count total number of clients in each range, you'd do that > with a series of (sub)queries like yours, all UNIONed together -- yes, you > have to add another subquery if you add another range > > any more complex than a few ranges, and you might want to set up a range > table with low and high balance values that the client table can join to > > let me know if you want more info > > rudy > http://rudy.ca/ > > > - > You are subscribed to the CFUGToronto CFTALK ListSRV. > This message has been posted by: "rudy" <[EMAIL PROTECTED]> > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ > This System has been donated by Infopreneur, Inc. > (http://www.infopreneur.net) > - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: "Mike" <[EMAIL PROTECTED]> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net)
