I am running into a problem where I have to use an alias (Ordernum) in a group by. I have found that this could be done somehow with subquery but I don't have any success with that and also those are not so familiar. Any kind of advice at the moment would be a major help.

SELECT a.ma   as  Document
     , a.ca  as  type
     , SUM(a.num) as Value1
     , SUM(a.num2)as Value2
     , coalesce(a.ba, MAX(a.ba) OVER (partition by a.ca)) as OrderNum  <-- Required in group by
     , a.ti as Year
FROM Orders a
WHERE ti = 2020
       , b.ca
       , a.ti
  • I see unclosed round bracket for coalesce(... and typo in the WHERE - b.ca instead of a.ca
    – Kondybas
    Sep 29, 2020 at 14:15
  • Yes made the changes, those were mistakes as copying the query here. That part of the query works, just need the solution for that grouping alias problem.
    – Meettu
    Sep 29, 2020 at 14:23

1 Answer 1


You can use a outer SELECT or A cte to accomplish that

    ,SUM(Value1) AS Value1
    ,SUM(Value2) AS Value2
        a.ma   as  Document
        ,a.ca  as  type
        ,SUM(a.num) as Value1
        ,SUM(a.num2)as Value2
        ,coalesce(a.ba, MAX(a.ba) OVER (partition by a.ca)) as OrderNum  
        ,a.ti as Year
    FROM Orders a
    WHERE ti = 2020) t1

Group by 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.