locked
Add Column to Query On the Fly? RRS feed

  • Question

  • Hi,

    I'm trying to sort/order this query the way I'd like but I don't have the option of adding a physical column to the table called sort_order.  That would be the ideal way to do it, but I'm thinking there is a way using a temp table but I can't seem to get it right. 

     

    I'm trying to get the results in this order "Elementary, Middle, High".  

    My query is below:

    SELECT
              CASE
            WHEN jl.grade_level IN ( 'Pre-K','K','1','2','3','4','K-4' )    THEN 'Elementary'
            WHEN jl.grade_level IN ( '5-8','5','6','7','8' )    THEN 'Middle'
            WHEN jl.grade_level IN ( '9','10','11','12','9-12','High') THEN 'High'
           
        END as grade_level,
       
        COUNT(jl.grade_level) AS NumJobsByGrade
       
        FROM JobList jl
         
        WHERE jl.grade_level <> ''
       
        
        GROUP BY CASE
            WHEN jl.grade_level IN ( 'Pre-K','K','1','2','3','4','K-4' )    THEN 'Elementary'
            WHEN jl.grade_level IN ( '5-8','5','6','7','8' )  THEN 'Middle'
            WHEN jl.grade_level IN ( '9','10','11','12','9-12','High') THEN 'High'
           
        END
      
        ORDER BY grade_level

     

    Here are the way the results look. 

    grade_level |  NumJobsByGrade

    Elementary , 2

    High, 5

    Middle, 2

     

    I'm trying to get them in this order "Elementary, Middle, High".  

    Any help appreciated...

    -Westside2008



    Tuesday, March 29, 2011 5:12 PM

Answers

  • You can in this particular case repeat the exact same expression in the ORDER BY or alternatively give a different name to the resulting column (Say, GradeLevel) and use this alias or use ordinal position.

    BTW, for your amusement and to be aware of possible problems I suggest this blog post

    The Troll's Puzzle - a SQL fable

    Update, I see you want to order them not in the character order. You need to use 

    SELECT  f.grade_level, 
         Count(jl.grade_level) AS numjobsbygrade 
    FROM   joblist jl 
         CROSS APPLY (SELECT CASE 
                    WHEN jl.grade_level IN ('Pre-K','K','1','2', 
                                '3','4','K-4') THEN 'Elementary' 
                    WHEN jl.grade_level IN ('5-8','5','6','7', 
                                '8') THEN 'Middle' 
                    WHEN jl.grade_level IN ('9','10','11','12', 
                                '9-12','High') THEN 'High' 
                   END AS grade_level) f 
    WHERE  jl.grade_level <> '' 
    GROUP BY f.grade_level 
    ORDER BY CASE f.grade_level 
          WHEN 'Elementary' THEN 1 
          WHEN 'Middle' THEN 2 
          WHEN 'High' THEN 3 
         END

    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog



    • Marked as answer by Kalman Toth Tuesday, March 29, 2011 7:06 PM
    • Edited by Naomi N Wednesday, March 30, 2011 1:54 PM
    Tuesday, March 29, 2011 5:18 PM
  • WITH cte AS (SELECT
         CASE
        WHEN jl.grade_level IN ( 'Pre-K','K','1','2','3','4','K-4' )  THEN 'Elementary'
        WHEN jl.grade_level IN ( '5-8','5','6','7','8' )  THEN 'Middle'
        WHEN jl.grade_level IN ( '9','10','11','12','9-12','High') THEN 'High'
        
      END as grade_level
    FROM JobList jl
    WHERE jl.grade_level <> '')
    SELECT grade_level,
     COUNT(grade_level) AS NumJobsByGrade
    FROM cte
    GROUP BY grade_level
    ORDER BY CASE WHEN grade_level = 'Elementary' THEN 1
     WHEN grade_level = 'Middle' THEN 2
     WHEN grade_level = 'HIGH' THEN 3 END
    

    Tom
    • Marked as answer by Westside2008 Tuesday, March 29, 2011 5:31 PM
    Tuesday, March 29, 2011 5:24 PM

All replies

  • You can in this particular case repeat the exact same expression in the ORDER BY or alternatively give a different name to the resulting column (Say, GradeLevel) and use this alias or use ordinal position.

    BTW, for your amusement and to be aware of possible problems I suggest this blog post

    The Troll's Puzzle - a SQL fable

    Update, I see you want to order them not in the character order. You need to use 

    SELECT  f.grade_level, 
         Count(jl.grade_level) AS numjobsbygrade 
    FROM   joblist jl 
         CROSS APPLY (SELECT CASE 
                    WHEN jl.grade_level IN ('Pre-K','K','1','2', 
                                '3','4','K-4') THEN 'Elementary' 
                    WHEN jl.grade_level IN ('5-8','5','6','7', 
                                '8') THEN 'Middle' 
                    WHEN jl.grade_level IN ('9','10','11','12', 
                                '9-12','High') THEN 'High' 
                   END AS grade_level) f 
    WHERE  jl.grade_level <> '' 
    GROUP BY f.grade_level 
    ORDER BY CASE f.grade_level 
          WHEN 'Elementary' THEN 1 
          WHEN 'Middle' THEN 2 
          WHEN 'High' THEN 3 
         END

    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog



    • Marked as answer by Kalman Toth Tuesday, March 29, 2011 7:06 PM
    • Edited by Naomi N Wednesday, March 30, 2011 1:54 PM
    Tuesday, March 29, 2011 5:18 PM
  • Can you provide an example using the code I provided?  I think I know what you mean but not sure how to do this syntax wise.

     

     

    Tuesday, March 29, 2011 5:20 PM
  • WITH cte AS (SELECT
         CASE
        WHEN jl.grade_level IN ( 'Pre-K','K','1','2','3','4','K-4' )  THEN 'Elementary'
        WHEN jl.grade_level IN ( '5-8','5','6','7','8' )  THEN 'Middle'
        WHEN jl.grade_level IN ( '9','10','11','12','9-12','High') THEN 'High'
        
      END as grade_level
    FROM JobList jl
    WHERE jl.grade_level <> '')
    SELECT grade_level,
     COUNT(grade_level) AS NumJobsByGrade
    FROM cte
    GROUP BY grade_level
    ORDER BY CASE WHEN grade_level = 'Elementary' THEN 1
     WHEN grade_level = 'Middle' THEN 2
     WHEN grade_level = 'HIGH' THEN 3 END
    

    Tom
    • Marked as answer by Westside2008 Tuesday, March 29, 2011 5:31 PM
    Tuesday, March 29, 2011 5:24 PM
  •  

    SELECT
              CASE
            WHEN jl.grade_level IN ( 'Pre-K','K','1','2','3','4','K-4' )    THEN 'Elementary'
            WHEN jl.grade_level IN ( '5-8','5','6','7','8' )    THEN 'Middle'
            WHEN jl.grade_level IN ( '9','10','11','12','9-12','High') THEN 'High'
           
        END as grade_level,
       
        COUNT(jl.grade_level) AS NumJobsByGrade
       
        FROM JobList jl
         
        WHERE jl.grade_level <> ''

        GROUP BY CASE
            WHEN jl.grade_level IN ( 'Pre-K','K','1','2','3','4','K-4' )    THEN 'Elementary'
            WHEN jl.grade_level IN ( '5-8','5','6','7','8' )  THEN 'Middle'
            WHEN jl.grade_level IN ( '9','10','11','12','9-12','High') THEN 'High'
           
        END
      
        ORDER BY CASE
            WHEN jl.grade_level IN ( 'Pre-K','K','1','2','3','4','K-4' )    THEN 1
            WHEN jl.grade_level IN ( '5-8','5','6','7','8' )  THEN 2
            WHEN jl.grade_level IN ( '9','10','11','12','9-12','High') 3


    If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer. DVR
    Tuesday, March 29, 2011 5:25 PM
  • I seem to get this SQL error:

     

    Column "JobList.grade_level" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

    Any idea, I think I'm close here....

     

    Tuesday, March 29, 2011 5:28 PM
  • This worked Tom, I'd still like to figure out how to do it using the code I originally had because I'm sure it's possible, but your code worked fine.

     

    -WestSide2008

    Tuesday, March 29, 2011 5:31 PM
  • Yes, that worked as well... I will have to learn about using CROSS APPLY and CTE's.  They seem powerful.
    Tuesday, March 29, 2011 5:38 PM