sql - 如何计算列的平均值，然后将其包含在 SQL 中的选择查询中

Question

我有一个查询，结果如下所示

saledate    Amount    TRVal
20/05/2013  $6250.78  4
21/05/2013  $4562.23  4
22/05/2013  $565.32   6
23/05/2013  $85.36    8
24/05/2013  $56.36    5

我想要像这样的平均金额

saledate    Amount    TRVal  AvgVal
20/05/2013  $6250.78  4      2304.01
21/05/2013  $4562.23  4      2304.01
22/05/2013  $565.32   6      2304.01
23/05/2013  $85.36    8      2304.01
24/05/2013  $56.36    5      2304.01

我知道要计算平均值，但我希望它包含在查询结果中。

我正在使用的查询是

    Select ISNULL([SaleDate],'Totals') AS [Totals], TempSaleDate, Domestic, Export, Import, TotalJobs,
                  '$' + CAST(CAST(Value AS DECIMAL(10,2)) AS VARCHAR(15)) Value, 
                  '$' + CAST(CAST(ValueNOGST AS DECIMAL(10,2)) AS VARCHAR(15)) ValueNOGST, 
                  Cancelled,
                  '$' + CAST(CAST(CancelledValue AS DECIMAL(10,2)) AS VARCHAR(15)) CancelledValue,
                  '$' + CAST(CAST(CancelledValueNOGST AS DECIMAL(10,2)) AS VARCHAR(15)) CancelledValueNOGST,
                  '$' + CAST(CAST(TotalValue AS DECIMAL(10,2)) AS VARCHAR(15)) TotalValue,
                  '$' + CAST(CAST(TotalValueNOGST AS DECIMAL(10,2)) AS VARCHAR(15)) TotalValueNOGST,
                  (select AVG(TotalValue) from sales) as FFF,
                  TotalGST, TotalValue+TotalGST TotalWithNOGSTCheck

                  FROM (

                    select convert(varchar(10), sales.saledate, 103) [SaleDate],max(sales.SaleDate) [TempSaleDate], SUM(sales.Domestic) [Domestic], SUM(sales.Export) [Export], SUM(sales.Import) [Import], 
                    (SUM(sales.Domestic) + SUM(sales.Export) + SUM(sales.Import)) AS TotalJobs,
                    SUM(sales.Value) [Value], SUM(sales.ValueNoGST) [ValueNOGST],
                    Sum(sales.Cancelled) [Cancelled], 
                    sum(sales.cancelledValue) [CancelledValue],
                    sum(sales.CancelledValueNOGST) [CancelledValueNOGST],
                    SUM(sales.totalValue) [TotalValue],
                     SUM(sales.TotalValueNOGST) [TotalValueNOGST],
                    SUM(sales.FGST) [FreightGST],SUM(sales.WGST) [WarrantyGST],SUM(sales.TGST) [TotalGST]
                    from
                    (
                    select TOP 100 PERCENT max(j.SaleDate) SaleDate,
                    case when max(oc.Code) = 'AU' and max(dc.Code) = 'AU' then 1 else 0 end [Domestic],
                    case when max(oc.Code) = 'AU' and max(dc.Code) <> 'AU' then 1 else 0 end [Export],
                    case when max(oc.Code) <> 'AU' and max(dc.Code) = 'AU' then 1 else 0 end [Import],
                    1 [Total],
                    MAX(charges.FreightGst) [FGST],
                    MAX(charges.warrantygst) [WGST],
                    MAX(charges.totalgst) [TGST],
                    max(ic.Total-charges.totalgst) [Value],
                      max(ic.Total) [ValueNoGST],
                    case when max(c.CancelDate) is not null then 1 else 0 end [Cancelled],

                    case when max(c.CancelDate) is not null then max(ic.Total) else 0 end [CancelledValueNOGST],
                    case when max(c.CancelDate) is null then max(ic.Total) else 0 end [TotalValueNOGST],

                    case when max(c.CancelDate) is not null then max(ic.Total-charges.totalgst) else 0 end [CancelledValue],
                    case when max(c.CancelDate) is null then max(ic.Total-charges.totalgst) else 0 end [TotalValue]
                    from invoices i
                    left join Jobs j on i.JobKey = j.JobKey
                    inner join tasks t on j.jobkey = t.jobkey
                    inner join Consignments c on t.TaskKey = c.consignmentkey
                    inner join places op on c.originplacekey = op.placekey
                    inner join places dp on c.destinationplacekey = dp.placekey
                    inner join places oC on dbo.ParentPlaceKey(c.originPlaceKey) = oc.placekey
                    inner join places dC on dbo.ParentPlaceKey(c.destinationplacekey) = dc.placekey
                    left join (select consignmentKey, sum(Value) [Value] from ConsignmentItems ci group by consignmentkey ) ci on ci.ConsignmentKey = c.ConsignmentKey
                    left join (
                    select invoicekey, 
                    sum(case when ci.ChargeItemKey = 'FRT_SLL' then oc.Value else 0 end) [Freight], 
                    sum(case when ci.ChargeItemKey = 'WTY_SLL' then oc.Value else 0 end) [Warranty],
                    sum(case when ci.ChargeType = 4 then oc.Value else 0 end) [Total]
                    from InvoiceCharges ic
                    left join OptionCharges oc on ic.OptionChargeKey = oc.OptionChargeKey
                    left join ChargeItems ci on oc.ChargeItemKey = ci.ChargeItemKey
                    group by invoicekey
                    ) ic on ic.invoicekey = i.InvoiceKey

                   left join (
                    select OptionKey [OptionKey], 
                    sum(case when ci1.ChargeItemKey = 'FRT_TX1' then oc1.Value else 0 end) [FreightGst], 
                    sum(case when ci1.ChargeItemKey = 'WTY_TX1' then oc1.Value else 0 end) [WarrantyGst],
                    sum(case when ci1.ChargeType = 3 then oc1.Value else 0 end) [TotalGst] 
                    from OptionCharges oc1
                    left join ChargeItems ci1 on oc1.ChargeItemKey = ci1.ChargeItemKey
                    group by optionkey
                    ) charges on charges.OptionKey = c.SelectedOptionKey


                    where 
                    j.SaleDate >= '20-May-2013'
                    and
                    j.operationalstorekey = dbo.StoreCode('AU-WEB')
                    and j.saledate is not null and SelectedOptionKey is not null
                    group by j.jobkey
                    ) sales
                    group by convert(varchar(10), sales.saledate, 103) WITH ROLLUP
                    ) AS SalesData order by TempSaleDate

我尝试添加

 (SELECT avg(TotalValue) FROM SalesData) as avgVal

但抛出 无效的对象名称 SalesData。

不确定我做错了什么。

Answer 1

在聚合 SUM 上使用窗口化 AVG。

SELECT
    DATEADD(dd, DATEDIFF(dd, 0, saledate), 0)
    , SUM(something) AS Amount
    , ?? AS TRVal -- no idea what it is
    , AVG(SUM(something)) OVER () AS AvgVal
FROM MyTable -- or whatever
GROUP BY DATEDIFF(dd, 0, saledate)