locked
i got error on group by query RRS feed

  • Question

  • hi

    i have this query:

    select row_number() over(order by Name) as Number,Name

    ,COUNT(Code) as Tqty from WorkTbl group by Name order by Tqty

    ,COUNT(Code) as Tqty2 from WorkTbl Where Status <> 'AA' and Status <> 'BB' group by Name order by Tqty2

    and i got error: Incorrect syntax near the keyword 'as'.
    but if i run this query it works fine: 
    select row_number() over(order by Name) as Number,Name
    ,COUNT(Code) as Tqty from WorkTbl group by Name order by Tqty
    i must have all columns in the query
    how to do it ?

    Thursday, October 27, 2011 3:37 PM

Answers

  • The question is not entirely clear, but I think you need this:

    select row_number() over(order by Name) as Number,Name
    
    ,COUNT(Code) as Tqty ,
    COUNT(case when Status NOT IN ('AA','BB') then Code END) as [Special Status Count]
    from WorkTbl 
    group by Name
    order by Tqty
    



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


    My blog
    • Marked as answer by E_gold Thursday, October 27, 2011 3:51 PM
    Thursday, October 27, 2011 3:45 PM