How can I use sum() value in SQL Server multiple times?-CodePudding

My table is:

CREATE TABLE [dbo].[tableStore] 
(
    [ID] INT NULL,
    [Price] DECIMAL(18, 2) NULL,
    [Discount] DECIMAL(18, 2) DEFAULT ((0)) NULL,

    CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED ([ID] ASC)
);

How can I use SUM() value in SQL Server multiple times?

Like this:

SELECT
    SUM(tableStore.Price) AS PriceTotal,
    PriceTotal - SUM(tableStore.Discount)
FROM 
    tableStore

How can I use this column [PriceTotal] for another operation?

CodePudding user response：

You can get the PriceTotal using a subquery

select 
    PriceTotal - DiscountTotal 
from 
    (select sum(price) as PriceTotal, sum(discount) as DiscountTotal 
     from tableStore) as A;

Alternatively, you can use the WITH keyword to get a common table expression with the PriceTotal column:

with PriceTotalTable as 
(
    select sum(price) as pt 
    from tableStore
) 
select pt 
from PriceTotalTable;

Above queries are tested in MySQL