0

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
GROUP BY a.ma
       , 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 '20 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 '20 at 14:23
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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