[dba-SQLServer] summing in a crosstab

I think that I'd approach this by first building a query that contains a
calculated column for the ranges as described. To keep the query simple, I'd
create a function that returns the specified number. Something like this:

Function Ranger( BoatLength as Int ) as Int
    Dim result as Int
    Select Case BoatLength
           Case 10 to 15
                 result = 15
           Case 16 to 20
                 result = 20
           'etc.
    End Select
    Ranger = result
End Function

With that in hand, the rest is a cinch. Add a column to the query that calls
the Ranger() function and cross-tab that.

hth,
Arthur

On 11/12/07, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> I am doing a count like this
>         10 11 12 13 14 15 16 17 etc
> AK
> AL
> AR
> AZ
>
> This is by state, by boat length.  However the user wants the boat lengths
> grouped, i.e. a single column for 10-15, 15-20 etc. where I have summed
> the
> counts for 10,11,12,13,14, and 15 and display that in a single column.  Is
> this possible in a single query or am I going to have to go to a separate
> query to sum the lengths by state and then add that in to the crosstab
> somehow?
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>