Databases
StackOverflow2013
Postsdbo
POHow to calculate average of a column and then include it in a select query in SQL
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to calculate average of a column and then include it in a select query in SQL
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload