none
Group Clause in Common Table Expression

    Question


  • I have GROUP BY clause in Common Table Expression like folowing

    with CTEtable(MyID, MyName, MyValue1, MyValue2)

    (Select MyID, MyName,

       Count(MyV1) as MyValue1,

     case when MyName = 'MyName' then count(MyV1) end as MyValue2

    from myTable

    Group by MyID, MyName

    )

    I always get same value of MyValue1 and MyValues2 in CTETable. I see informaion from MSDN that Group Clause does not support is CTE  recursive.

     It does not give me error message , but it seems that it gives me not right result.

    I just wonder can I use Group By clause in CTE like above.

    Your help and information is great appreciated,

    Regards,

    Sourises,

    Monday, December 03, 2012 8:17 PM

Answers

  • Try this:

    with CTEtable(MyID, MyName, MyValue1, MyValue2)
     
    (Select MyID, MyName, 
    
       Count(MyV1) as MyValue1,
     
     sum(case when MyName = 'MyName' then 1 else 0 end) as MyValue2
     
    from myTable
     
    Group by MyID, MyName
     
    ) 
    


    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    • Marked as answer by sourises Tuesday, December 04, 2012 3:03 PM
    Monday, December 03, 2012 9:15 PM
  • Can you post some input data and desired output based on that input? 

    If you change your count expression to

    COUNT(case when MyName = 'MyName' then 1 end) as MyValue2

    you will still get 0 for most of the values and 1 for MyName = 'MyName' as you're grouping by MyName column.

    If you remove that column from the list of columns and GROUP BY, then you'll get different values for MyValue1 and MyValue2


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


    My blog

    Monday, December 03, 2012 9:23 PM
    Moderator

All replies

  • Try this:

    with CTEtable(MyID, MyName, MyValue1, MyValue2)
     
    (Select MyID, MyName, 
    
       Count(MyV1) as MyValue1,
     
     sum(case when MyName = 'MyName' then 1 else 0 end) as MyValue2
     
    from myTable
     
    Group by MyID, MyName
     
    ) 
    


    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    • Marked as answer by sourises Tuesday, December 04, 2012 3:03 PM
    Monday, December 03, 2012 9:15 PM
  • Can you post some input data and desired output based on that input? 

    If you change your count expression to

    COUNT(case when MyName = 'MyName' then 1 end) as MyValue2

    you will still get 0 for most of the values and 1 for MyName = 'MyName' as you're grouping by MyName column.

    If you remove that column from the list of columns and GROUP BY, then you'll get different values for MyValue1 and MyValue2


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


    My blog

    Monday, December 03, 2012 9:23 PM
    Moderator
  • Thanks a million for the information and help,

    Regards,

    Iccsi,

    Tuesday, December 04, 2012 3:04 PM