0

I am trying to sum a column that has an alias name (since to create the column I had to do math on other columns). I know that you can't do this (aggregate functions on aliases created within the same select query) but none of the solutions I have searched seem to work for me.

This my code

Select 
o.Order_Value Total_Price_Before_Discount, 
od.Price*od.Quantity_Ordered as Line_Price_Before_Discount, 
od.Discount_Value, od.Discount_Percentage, 
od.Price*od.Quantity_Ordered-od.Discount_Value as Line_Price_After_Discount,

sum(Line_Price_After_Discount) as Total_Price_After_Discount, -- this is the problem line

from Orders o (nolock)
join Order_Detail od (nolock) on 
o.Company_Code=od.Company_Code and o.Division_Code=od.Division_Code

I tried 

sum(od.Price*od.Quantity_Ordered-od.Discount_Value) as Total_Price_After_Discount

but that didn't work and I get a group by error

 invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Which I don't fully understand.

Carsten Massmann
  • 26,510
  • 2
  • 22
  • 43
Natan
  • 139
  • 2
  • 13
  • You have to repeat the expression or use a subquery (or CTE in MySQL 8+). Your code is not MySQL code, however. Please tag with the database you are really using. – Gordon Linoff Dec 28 '18 at 19:09
  • Sorry I made a mistake. I should have tagged the correct database now. Can you explain what you mean by repeating the expression or using a subquery? – Natan Dec 28 '18 at 19:12
  • 1
    you are missing a Group By if you are going to Use the `SUM` function. – Richard Hubley Dec 28 '18 at 19:14
  • By "repeating the expression" he meant what you tried in the second code block of your question; the comment above me addresses why your query still failed after making that change. You must GROUP BY every non-aggregated field referenced in the SELECT list. – Uueerdo Dec 28 '18 at 19:18

2 Answers2

0

If you want to keep existing values with sum you need to use OVER clause with PARTITION BY. If you change your script with following it should work.

Also, are you sure your join is correct? Company_Code and Division_Code are odd to join Order and Order_Detail tables. 

Select 
    o.Order_Value Total_Price_Before_Discount, 
    od.Price*od.Quantity_Ordered as Line_Price_Before_Discount, 
    od.Discount_Value, od.Discount_Percentage, 
    od.Price*od.Quantity_Ordered-od.Discount_Value as Line_Price_After_Discount,

    --sum(Line_Price_After_Discount) as Total_Price_After_Discount, -- this is the problem line
    SUM(od.Price*od.Quantity_Ordered-od.Discount_Value) OVER(PARTITION BY o.Company_Code,o.Division_Code) AS Total_Price_After_Discount

from Orders o (nolock)
join Order_Detail od (nolock) on o.Company_Code=od.Company_Code and o.Division_Code=od.Division_Code
Zeki Gumus
  • 1,484
  • 7
  • 14
0

A quick fix is to wrap your query in a CTE or sub-query, and then move the aggregation outside of it

select 
    *,
    sum(Line_Price_After_Discount) as Total_Price_After_Discount
from(
    Select 
       o.Order_Value Total_Price_Before_Discount, 
       od.Price*od.Quantity_Ordered as Line_Price_Before_Discount, 
       od.Discount_Value, 
       od.Discount_Percentage, 
       od.Price*od.Quantity_Ordered-od.Discount_Value as Line_Price_After_Discount
    from Orders o (nolock)
    join Order_Detail od (nolock) on 
    o.Company_Code=od.Company_Code and o.Division_Code=od.Division_Code
    ) x
group by 
    Total_Price_Before_Discount,
    Line_Price_Before_Discount,
    Discount_Value,
    Discount_Percentage,
    Line_Price_After_Discount
S3S
  • 24,809
  • 5
  • 26
  • 45