Grouping

  • Hello,

    Please if someone has an idea to the following

    I have a table as :

    Yr                   Total               Ag

    1990                 15                  19               

    1990                  30                 50

    1990                  40                  30

    1995                  30                   25

    1995                  60                   6

    1995                  10                   52

    I want to make it as following:

    Yr           Total                   Grp

    1990         15                       1-20

    1990          70                     21-55

    1995          60                      1-20

    1995          40                       21-55

     

    The Grp column is the range of the Ag column.

     

    Thanks

     

  • I'm not really sure what you are asking for?  Are you looking at renaming the Ag column to Grp and converting the data over?  Or is Grp going to be a new column in your table?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • It's a new column which will group the Ag values like from 1 To 20 then the total will be Sum for that range based on the Year.

    For the range of 1 -20 and for the Yr of 1990 we have only one value which is 15 therfore it dispalys as

    1990         15          1-20

    And so on . 

  • So are you looking for the T-SQL syntax to initialy populate this new column?  I would write a case statement to check and see where all of your ag values fall within whatever ranges you have in mind and populate the grp column accordingly. 

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • To get the results the way you want you;ll need to union.

    select Yr, sum(Case when AG between 1 and 20 then Total end) as 'Total', '1-20' as GRP

    from testtable

    group by yr

    union

    select Yr, sum(Case when AG between 21 and 55 then Total end) as 'Total', '21-55'

    from testtable

    group by yr

    order by Yr,GRP

     

    But it looks better like this

    select Yr, sum(Case when AG between 1 and 20 then Total end) as '1-20',

               sum(Case when AG between 21 and 55 then Total end) as '21-55'

    from testtable

    group by yr

    Results

    Yr,     1-20,   21-55

    1990, 15,      70

    1995, 60,      40

  • Hi,

    I did it using a temp table that contained the original fields plus a field that had a range as a string named AgeGroup. Then I just grouped by AgeGroup in another statement in the same query.

    Select Field1, Field2, Field3, Age

    ,case

     when Age <35 then '< 35'

     when (Age >=35 AND Age < 45) then '35-44'

     when (Age >=45 AND Age <55) then '45-54'

     when (Age >=55 AND Age <65) then '55-64'

     when (Age >=65 AND Age <75) then '65-74'

     when (Age >=75) then '75+'

     when Age is Null then 'No Response'

     else 'No Response'

     end

    as AgeGroup

    Into ##TempTable2

    from OriginalTable

    ----------------------------------

    <Select Statement towards ##TempTable2 with grouping by AgeGroup goes here>

    Regards,Yelena Varsha

  • What about this - no hard coding of values and should be easily adapted to many different situations...

    declare @ranges table(minRange int, maxRange int)
    insert into @ranges(minRange, maxRange)
    select 1, 20
    UNION
    select 21, 55
    UNION
    select 56, 100
    declare @data table(yr int, total int, ag int)
    insert into @data(yr, total, ag)
    select
    1990, 15, 19                
    UNION select
    1990, 30, 50
    UNION select
    1990, 40, 30
    UNION select
    1995, 30, 25
    UNION select
    1995, 60, 6
    UNION select
    1995, 10, 52
    select yr as yr, sum(total) as total, cast(minRange as varchar(5)) + '-' + cast(maxRange as varchar(5)) as grp
    from @data D
      inner join @ranges R
        on D.ag between R.minRange and R.maxRange
    group by yr, minRange, maxRange

     

  • BTW... output is

    yr          total       grp         
    ----------- ----------- ----------- 
    1990        15          1-20
    1990        70          21-55
    1995        60          1-20
    1995        40          21-55
  • Thanks a lot for all of you.

    Regards.

  • Select

         Yr,

         (Case 

            When Ag <=20 Then '1-20'

            When (Ag >=21 AND Ag <= 55) Then '21-55'

            When Age >=56 Then '56+'

            End) as Grp,

         Sum(Total) as Total

    From yourTable

    Group By

         Yr,

         (Case 

            When Ag <=20 Then '1-20'

            When (Ag >=21 AND Ag <= 55) Then '21-55'

            When Age >=56 Then '56+'

            End)

     

Viewing 10 posts - 1 through 9 (of 9 total)

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