Using alias in Group by 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  &lt;-- Required in group by
     , a.ti as Year
FROM Orders a
WHERE ti = 2020
GROUP BY a.ma
       , b.ca
       , a.ti
You can use a outer SELECT or A cte to accomplish that
SELECT 
    Document
    ,type
    ,SUM(Value1) AS Value1
    ,SUM(Value2) AS Value2
    ,OrderNum  
    ,Year
FROM 
    (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  
        ,a.ti as Year
    FROM Orders a
    WHERE ti = 2020) t1

Group by 
    Document
    ,type
    ,OrderNum  
    ,Year