Post reply

using a group by with temp column

  • November 10, 2010 at 2:53 am

    #99090

    Hi, Trying

    trying to do a select on a unique spellID and do a count by age group, similar to a pivot?

    eg. age count

    16+ 20

    16-24 25

    See below:

    Select

    Distinct NEW_Spell_ID,

    Case

    When DateDiff(yy,Date_of_Birth,Admission_Date) <16

    then '<16'

    When DateDiff(yy,Date_of_Birth,Admission_Date) < 25

    then '16-24'

    End as [Age_Group] , count(*)

    From #temp2008_11

    where CommissionerCode = 'TAM00'

    group by

    Case

    When DateDiff(yy,Date_of_Birth,Admission_Date) <16

    then '<16'

    When DateDiff(yy,Date_of_Birth,Admission_Date) < 25

    then '16-24'

    End , NEW_Spell_ID

  • November 10, 2010 at 9:52 am

    #1249014

    This looks like it should work. What kind of problem are you having.

    You might want to take a look at the first article in my signature. Posting CREATE TABLE and INSERT statements with sample data that shows your problem helps us out a lot.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • November 10, 2010 at 9:54 am

    #1249017

    What precisely is a "temp column"? A column in a temp table?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 11, 2010 at 2:44 am

    #1249363

    Thanks guys, i've managed to solve it

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

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