Як разлічыць сярэдняе значэнне слупка, а затым ўключыць яго ў запыце на выбарку ў SQL

У мяне ёсць запыт, дзе вынік, як наступнае

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

Я ведаю, што для вылічэнні значэння Avg, але я хачу яго ў камплекце з вынікам запыту.

Запыт я выкарыстоўваю гэта

    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 </​​код>.

Не ўпэўнены, што я раблю няправільна.

2
Так, калі ласка, што СКБД і версія, якую вы выкарыстоўваеце? Гэта сапраўды робіць розніцу.
дададзена аўтар ErikE, крыніца
Глядзіце мой абноўлены адказ.
дададзена аўтар egrunin, крыніца
Глядзіце мой абноўлены адказ.
дададзена аўтар egrunin, крыніца
Глядзіце мой абноўлены адказ.
дададзена аўтар egrunin, крыніца
SQL Server 2008
дададзена аўтар user1282609, крыніца
SQL Server 2008
дададзена аўтар user1282609, крыніца
Гэта выглядае як MSSQL, якую версію вы карыстаецеся?
дададзена аўтар Burhan Khalid, крыніца

9 адказы

Карыстальнік аконнага тыпу 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)
2
дададзена

Карыстальнік аконнага тыпу 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)
2
дададзена

Карыстальнік аконнага тыпу 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)
2
дададзена
SELECT saledate, Amount, TRVal, (SELECT avg(amount) FROM tableA) as AvgVal FROM tableA

або

SELECT saledate, Amount, TRVal, avgVal FROM tableA
INNER JOIN (SELECT avg(amount) as AvgVal FROM tableA) x

<�Моцны> адрэдагаваць, каб дадаць:

Вы павінны замяніць SalesData </​​код> з імем табліцы вы вы запытваеце (якія вы выдалілі з вашага прыкладу:

FROM(
-- whatever name you put here is the one you need in place of SalesData
)

<�Моцны> адрэдагаваць, каб дадаць:

Your FROM clause is complicated, so MSSQL 2008 added the WITH clause fабо just such cases:

;WITH thisTable AS (
    -- put your FROM clause in here
)
SELECT /* list of fields */, (SELECT AVG(amount) FROM thisTable) as AvgVal
FROM thisTable

So fабо you:

; WITH thisTable AS (
select convert(varchar(10), sales.saledate, 103) [SaleDate],max(sales.SaleDate) [TempSaleDate],
    SUM(sales.Domestic) [Domestic], SUM(sales.Expабоt) [Expабоt], SUM(sales.Impабоt) [Impабоt], 
    (SUM(sales.Domestic) + SUM(sales.Expабоt) + SUM(sales.Impабоt)) 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 [Expабоt],
    case when max(oc.Code) <> 'AU' and max(dc.Code) = 'AU' then 1 else 0 end [Impабоt],
    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.абоiginplacekey = op.placekey
                inner join places dp on c.destinationplacekey = dp.placekey
                inner join places oC on dbo.ParentPlaceKey(c.абоiginPlaceKey) = 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.operationalstабоekey = dbo.StабоeCode('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
                )
/* end of definition of thisTable */ 
)
/* here's your select */
Select ISNULL([SaleDate],'Totals') AS [Totals], TempSaleDate, Domestic, Expабоt, Impабоt, 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,
/* here's the AVG() */
              (select AVG(TotalValue) from thisTable) as FFF,
              TotalGST, TotalValue+TotalGST TotalWithNOGSTCheck,

FROM thisTable
1
дададзена
Дзякуй за адказ, але глядзіце мой абноўлены запыт.
дададзена аўтар user1282609, крыніца
(Выберыце AVG (TotalValue) ад продажу), як ФФФ кідае --- недапушчальнае імя аб'екта «продажу».
дададзена аўтар user1282609, крыніца
SELECT saledate, Amount, TRVal, (SELECT avg(amount) FROM tableA) as AvgVal FROM tableA

або

SELECT saledate, Amount, TRVal, avgVal FROM tableA
INNER JOIN (SELECT avg(amount) as AvgVal FROM tableA) x

<�Моцны> адрэдагаваць, каб дадаць:

Вы павінны замяніць SalesData </​​код> з імем табліцы вы вы запытваеце (якія вы выдалілі з вашага прыкладу:

FROM(
-- whatever name you put here is the one you need in place of SalesData
)

<�Моцны> адрэдагаваць, каб дадаць:

Your FROM clause is complicated, so MSSQL 2008 added the WITH clause fабо just such cases:

;WITH thisTable AS (
    -- put your FROM clause in here
)
SELECT /* list of fields */, (SELECT AVG(amount) FROM thisTable) as AvgVal
FROM thisTable

So fабо you:

; WITH thisTable AS (
select convert(varchar(10), sales.saledate, 103) [SaleDate],max(sales.SaleDate) [TempSaleDate],
    SUM(sales.Domestic) [Domestic], SUM(sales.Expабоt) [Expабоt], SUM(sales.Impабоt) [Impабоt], 
    (SUM(sales.Domestic) + SUM(sales.Expабоt) + SUM(sales.Impабоt)) 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 [Expабоt],
    case when max(oc.Code) <> 'AU' and max(dc.Code) = 'AU' then 1 else 0 end [Impабоt],
    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.абоiginplacekey = op.placekey
                inner join places dp on c.destinationplacekey = dp.placekey
                inner join places oC on dbo.ParentPlaceKey(c.абоiginPlaceKey) = 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.operationalstабоekey = dbo.StабоeCode('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
                )
/* end of definition of thisTable */ 
)
/* here's your select */
Select ISNULL([SaleDate],'Totals') AS [Totals], TempSaleDate, Domestic, Expабоt, Impабоt, 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,
/* here's the AVG() */
              (select AVG(TotalValue) from thisTable) as FFF,
              TotalGST, TotalValue+TotalGST TotalWithNOGSTCheck,

FROM thisTable
1
дададзена
Дзякуй за адказ, але глядзіце мой абноўлены запыт.
дададзена аўтар user1282609, крыніца
(Выберыце AVG (TotalValue) ад продажу), як ФФФ кідае --- недапушчальнае імя аб'екта «продажу».
дададзена аўтар user1282609, крыніца
SELECT saledate, Amount, TRVal, (SELECT avg(amount) FROM tableA) as AvgVal FROM tableA

або

SELECT saledate, Amount, TRVal, avgVal FROM tableA
INNER JOIN (SELECT avg(amount) as AvgVal FROM tableA) x

<�Моцны> адрэдагаваць, каб дадаць:

Вы павінны замяніць SalesData </​​код> з імем табліцы вы вы запытваеце (якія вы выдалілі з вашага прыкладу:

FROM(
-- whatever name you put here is the one you need in place of SalesData
)

<�Моцны> адрэдагаваць, каб дадаць:

Your FROM clause is complicated, so MSSQL 2008 added the WITH clause fабо just such cases:

;WITH thisTable AS (
    -- put your FROM clause in here
)
SELECT /* list of fields */, (SELECT AVG(amount) FROM thisTable) as AvgVal
FROM thisTable

So fабо you:

; WITH thisTable AS (
select convert(varchar(10), sales.saledate, 103) [SaleDate],max(sales.SaleDate) [TempSaleDate],
    SUM(sales.Domestic) [Domestic], SUM(sales.Expабоt) [Expабоt], SUM(sales.Impабоt) [Impабоt], 
    (SUM(sales.Domestic) + SUM(sales.Expабоt) + SUM(sales.Impабоt)) 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 [Expабоt],
    case when max(oc.Code) <> 'AU' and max(dc.Code) = 'AU' then 1 else 0 end [Impабоt],
    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.абоiginplacekey = op.placekey
                inner join places dp on c.destinationplacekey = dp.placekey
                inner join places oC on dbo.ParentPlaceKey(c.абоiginPlaceKey) = 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.operationalstабоekey = dbo.StабоeCode('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
                )
/* end of definition of thisTable */ 
)
/* here's your select */
Select ISNULL([SaleDate],'Totals') AS [Totals], TempSaleDate, Domestic, Expабоt, Impабоt, 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,
/* here's the AVG() */
              (select AVG(TotalValue) from thisTable) as FFF,
              TotalGST, TotalValue+TotalGST TotalWithNOGSTCheck,

FROM thisTable
1
дададзена
Дзякуй за адказ, але глядзіце мой абноўлены запыт.
дададзена аўтар user1282609, крыніца
(Выберыце AVG (TotalValue) ад продажу), як ФФФ кідае --- недапушчальнае імя аб'екта «продажу».
дададзена аўтар user1282609, крыніца

гэта самы просты код. Ён можа быць аптымізаваны, калі ён занадта павольна ...

select saledate, amount, trval, (select avg(amount) from  yourtable) as AvgVal from yourtable;
1
дададзена

гэта самы просты код. Ён можа быць аптымізаваны, калі ён занадта павольна ...

select saledate, amount, trval, (select avg(amount) from  yourtable) as AvgVal from yourtable;
1
дададзена

гэта самы просты код. Ён можа быць аптымізаваны, калі ён занадта павольна ...

select saledate, amount, trval, (select avg(amount) from  yourtable) as AvgVal from yourtable;
1
дададзена