Group by

Question

Post reply

Group by

Lfitz

SSC Veteran

Points: 203

November 17, 2006 at 3:48 am

#368071

Hi

At the moment i have this code for a report im writing

select sub.AgeRange,

      SUM(decode(sub.gender

                 ,'M'

,1

                 ,0)) male

      ,SUM(decode(sub.gender

                 ,'F'

,1

                 ,0)) female

from (select gender,date_of_birth, months_between (sysdate, date_of_birth) / 12 as age_in_years,

        round (months_between (sysdate, date_of_birth) / 12) as age,

case

when months_between (sysdate, date_of_birth) / 12 BETWEEN 20 AND 29.5 THEN '20-29'
when months_between (sysdate, date_of_birth) / 12 BETWEEN 30 AND 39.5 THEN '30-39'

when months_between (sysdate, date_of_birth) / 12 BETWEEN 40 AND 49.5 THEN '40-49'

when months_between (sysdate, date_of_birth) / 12 BETWEEN 50 AND 59.5 THEN '50-59'

when months_between (sysdate, date_of_birth) / 12 BETWEEN 60 AND 100 THEN '60 and More'

        ELSE 'No Birth Date Entered’

        END as AgeRange

from hr_person p) sub

group by sub.AgeRange

which gives me the following output

AGERANGE MALE FEMALE

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

20-29 1087 1215

30-39 1359 1408

40-49 608 690

50-59 409 454

60 and More 402 282

No Birth Date Entered 4162 5282

6 rows selected.

But i need to group by certain departments and i have to take the department from another table - temp_rates but every time i do this it crashes!

Can anyone shed any light

Thanks Lisa

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

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Isn't the from a previous post? Converting date of birth to Age Why not continue with that previous one so people have a tract of thought to follow?

I wasn't born stupid - I had to study.

Loner SSC-Insane Points: 21279 More actions · Answer 2

The whole query looked liked an Oracle query.

Decode, month_between, sysdate are all oracle terms.

Do you want to write an Oracle query or SQL Server query?

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

Farrell has already posted a link to what seems to be another forum in which this question was asked....let's all work together in making sure we discourage cross-posting as much as we can and post our responses to just one forum...

**ASCII stupid question, get a stupid ANSI !!!**