Post reply

Query returning counts for value ranges

  • August 22, 2006 at 9:48 am

    #191956

    I have a table that contains a bunch of information but one of the fields is "cosize" which represents the number of employees for each company record.  I am trying to write a query that will give me a count of the companies in ranges incremented by 10.  For instance, I want to know the number of companies that have 1 - 9 employees, 10 - 19 employees, 20 - 29 employees and so on and so on.  I can write a bunch of individual queries to get this but I'm trying to do it with one query.  I've tried a bunch of different things with no luck.  If anyone has some ideas I could try that would be great.  Thanks.

  • August 22, 2006 at 9:59 am

    #656149

    This works as long as cosize is an integer type:

    Create Table #test (

      cosize int

    )

    Insert Into #Test

    Select 1 Union All

    Select 4 Union All

    Select 9 Union all

    Select 35 Union All

    Select 95 Union All

    Select 99 Union All

    Select 91 Union All

    Select 500 Union All

    Select 508 

    Select LowerRange, UpperRange, Count(*)

    From

    (

      Select ((cosize / 10) * 10) As LowerRange, ((Cosize / 10) * 10) + 9 As UpperRange

      From #Test

    ) dt

    Group By LowerRange, UpperRange

    Order By LowerRange

  • August 22, 2006 at 4:47 pm

    #656248

    Thanks for your help PW.  This has me going in the right direction now.  Found out I had to make a few modifications because of the state of our data but this definitly helped.  Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply