Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Group Clause in Common Table Expression

Answered Group Clause in Common Table Expression

  • Monday, December 03, 2012 8:17 PM
     
     
    Sign In to Vote
    0


    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,

     

All Replies

  • Monday, December 03, 2012 9:15 PM
     
     Answered Has Code
    Sign In to Vote
    0

    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:23 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    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

     
  • Tuesday, December 04, 2012 3:04 PM
     
     
    Sign In to Vote
    0

    Thanks a million for the information and help,

    Regards,

    Iccsi,