I used over partition to create a table with SalespersonID, AvgLineProfit, AvgLineProfitPerSalesPerson.

select distinct
    invoices.SalespersonPersonID,
    sum(invoicelines.Quantity) over(partition by invoices.SalespersonPersonID) as QuantityPerSalesPerson,
    avg(invoicelines.LineProfit) over() as AvgLineProfit,
    avg(invoicelines.LineProfit) over(partition by invoices.SalespersonPersonID) as AvgProfitPerSalesPerson
from Sales.InvoiceLines as invoicelines
join Sales.Invoices as invoices on invoicelines.InvoiceID = invoices.InvoiceID
order by invoices.SalespersonPersonID

Now I want to add another column with the SalesPersonID if AvgLineProfitPerSalesPerson > AvgLineProfit, or NULL if not.

How can I use the created columns in my condition?

I tried creating new select query or using 'case when'

case when AvgProfitPerSalesPerson > AvgLineProfit then SalespersonPersonID over() as new_column

but it doesn't work.

Here is how the output looks like

CodePudding user response：

You can't refer to aliases in the same select. so you have to repeate the whole thing:

select ...
, case when avg(invoicelines.LineProfit) over(partition by invoices.SalespersonPersonID) > avg(invoicelines.LineProfit) over() then SalespersonPersonID end as new_column

or wrap your original select in a subquery then it works:

select *, case when case when AvgProfitPerSalesPerson > AvgLineProfit...
from (
select distinct
    invoices.SalespersonPersonID,
    sum(invoicelines.Quantity) over(partition by invoices.SalespersonPersonID) as QuantityPerSalesPerson,
    avg(invoicelines.LineProfit) over() as AvgLineProfit,
    avg(invoicelines.LineProfit) over(partition by invoices.SalespersonPersonID) as AvgProfitPerSalesPerson
from Sales.InvoiceLines as invoicelines
join Sales.Invoices as invoices on invoicelines.InvoiceID = invoices.InvoiceID
) x
order by SalespersonPersonID

CodePudding user response：

In this way it works

select 
new_table.SalespersonPersonID,
new_table.QuantityPerSalesPerson,
new_table.AvgLineProfit,
new_table.AvgProfitPerSalesPerson,
case when new_table.AvgProfitPerSalesPerson > new_table.AvgLineProfit then new_table.SalespersonPersonID
end as succesfull_salesperson
from
(select distinct
    invoices.SalespersonPersonID,
    sum(invoicelines.Quantity) over(partition by invoices.SalespersonPersonID) as QuantityPerSalesPerson,
    avg(invoicelines.LineProfit) over() as AvgLineProfit,
    avg(invoicelines.LineProfit) over(partition by invoices.SalespersonPersonID) as AvgProfitPerSalesPerson
from Sales.InvoiceLines as invoicelines
join Sales.Invoices as invoices on invoicelines.InvoiceID = invoices.InvoiceID) as new_table
order by SalespersonPersonID