title: How to calculate average of a column and then include it in a select query in SQL
body: <p>I have a query where the result like the following</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I want the Average of Amount like </p>
  
  <pre><code>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
  </code></pre>
  
  <p>I know to calculate the avg value but i want it included with the result of query.</p>
  
  <p>The query i am using is</p>
  
  <pre><code>    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) &lt;&gt; 'AU' then 1 else 0 end [Export],
                      case when max(oc.Code) &lt;&gt; '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 &gt;= '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
  </code></pre>
  
  <p>I tried to add  </p>
  
  <pre><code> (SELECT avg(TotalValue) FROM SalesData) as avgVal
  </code></pre>
  
  <p>but throws <code>invalid object name SalesData</code>.</p>
  
  <p>Not sure what I am doing wrong.</p>