CODEWITHSUNDEEP
sqlsql-servert-sqlazure-sql-database
Richard Vanbergen
Richard Vanbergen

Reputation: 1974

Aggregate function results in select statement

Hopefully the code below should demonstrate what I'm trying to achieve.

The issue is that none of the input selects are resolved by the time I try to calculate VatableCash so I get "Invalid Column" when trying to select it.

Sorry if there's something plainly obvious I can do here. SQL isn't one of my strong suits.

select
    OrderHeader.ID,
    sum(OrderLine.NetPrice) as OrderLineNetPrice,
    sum(OrderLine.GrossPrice) as OrderLineGrossPrice,
    sum(
        case when PaymentOption_ID = 8
            then Payment.Amount
            else 0
        end
    ) as TotalCashAmount,
    ((OrderLineGrossPrice - OrderLineNetPrice) / OrderLineGrossPrice) * TotalCashAmount as VatableCash
    from OrderHeader
    inner join Payment on Payment.OrderHeader_ID = OrderHeader.ID
    inner join OrderLine on OrderLine.OrderHeader_ID = OrderHeader.ID
    group by OrderHeader.ID

Upvotes: 1

Views: 75

Answers (2)

George Menoutis
George Menoutis

Reputation: 7240

Love the cross apply! Use it whenever you want some handy extra columns.

select
    OrderHeader.ID,
    sum(OrderLine.NetPrice) as OrderLineNetPrice,
    sum(OrderLine.GrossPrice) as OrderLineGrossPrice,
    TotalCashAmount,
    ((OrderLineGrossPrice - OrderLineNetPrice) / OrderLineGrossPrice) * TotalCashAmount as VatableCash
    from OrderHeader
    inner join Payment on Payment.OrderHeader_ID = OrderHeader.ID
    inner join OrderLine on OrderLine.OrderHeader_ID = OrderHeader.ID
    cross apply ( select sum(
        case when PaymentOption_ID = 8
            then Payment.Amount
            else 0
        end
    )) as subquery(TotalCashAmount)
    group by OrderHeader.ID

Upvotes: 2

Serkan Arslan
Serkan Arslan

Reputation: 13393

You need to use sub query.

you can try this.

;WITH CTE AS
(
    select
        OrderHeader.ID,
        sum(OrderLine.NetPrice) as OrderLineNetPrice,
        sum(OrderLine.GrossPrice) as OrderLineGrossPrice,
        sum(
            case when PaymentOption_ID = 8
                then Payment.Amount
                else 0
            end
        ) as TotalCashAmount
    from OrderHeader
        inner join Payment on Payment.OrderHeader_ID = OrderHeader.ID
        inner join OrderLine on OrderLine.OrderHeader_ID = OrderHeader.ID
    group by OrderHeader.ID
)
SELECT *, 
    ((OrderLineGrossPrice - OrderLineNetPrice) / OrderLineGrossPrice) * TotalCashAmount as VatableCash
FROM CTE

Upvotes: 2

Related Questions