Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    21

    Unanswered: Help with complicated SQL query

    Hi everyone.

    This is my scenario:

    I have two tables:
    Code:
    persons (id,age,roleid)
    roles (roleid,description)
    I want to build a sql query to produce the following rows (example):
    Code:
    range(age)   role1   role2   role3 .... rolen
    0 to 4         11       24      5           7
    5 to 9         42        7      1           0
    10 to 14       14       21      9           8
    15 to 20       7         0      7          19
    I was reading an information concerning to ROLLUP and CUBE but I have no idea how to do a query like this.

    Thanks for all your help!

    Roland

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you want a cross tab query using the CASE statement. Read about both in books online and come back if you are still having trouble.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Lookup "Crosstab" in Books Online.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this isn't quite as straightforward as it first appears
    Code:
    select range
         , sum(case when roleid =1
                    then rows else 0 end) as role1
         , sum(case when roleid =2
                    then rows else 0 end) as role2
         , ...
         , sum(case when roleid =n
                    then rows else 0 end) as rolen
      from (
           select '0 to 4' as range
                , roleid
                , count(*) as rows
             from persons
            where age between 0 and 4
           group by roleid 
           union all                      
           select '5 to 9' as range
                , roleid
                , count(*) as rows
             from persons
            where age between 5 and 9
           group by roleid 
           union all
           select '10 to 14' as range
                , roleid
                , count(*) as rows
             from persons
            where age between 10 and 14
           group by roleid 
           union all
           select '15 to 20' as range
                , roleid
                , count(*) as rows
             from persons
            where age between 15 and 20
           group by roleid 
           ) as dt      
    group by range
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2004
    Posts
    21
    Great r937!! thanks!! everything worked perfectly!

    Roland

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •