title: SQL Compare stock table, Average Price + 10% Round up & Update Field
body: <p>I have a SQL table called <code>tblStock</code>. It holds the details of thousands of items of stocks sold by my company. It has the following columns:</p>
  
  <pre><code>StockID
  Make
  Model
  YearManufacture
  Price
  GuidePrice
  </code></pre>
  
  <p>For unsold items in this table I want to add the predicted sales value into the field called <code>GuidePrice</code>, based on the figures for stock already sold,
  e.g if I have the below item:</p>
  
  <pre><code>StockID: 123456
  Make: VW
  Model: Beetle
  YearManufacture: 1975
  Price: 2000.00
  GuidePrice:
  </code></pre>
  
  <p>I want to calculate a guide price based on the prices I’ve got for all the previous 1975 VW Beetles I’ve sold.</p>
  
  <p>I want to take the mean average price for all the previous 1975 VW Beetles in <code>tblStock</code> and add 10%, then round up to the nearest 500.00 and insert in the <code>GuidePrice</code> column.</p>
  
  <p>The overall calculation is more complicated than this, but this is the part I’m stuck on. I’d appreciate if anyone could give me some tips!</p>
  
  <p>EDIT:
  This works to get the average, but I'm having to type in each Make, Model &amp; Year
  (And ROUND up after)</p>
  
  <pre><code>Update tblStock
  set GuidePrice=(Select  AVG(Price) 
  from tblStock 
  INNER JOIN
  tblMake ON tblStock.Car_idMake = tblMake.idMake INNER JOIN
  tblModel ON tblStock.Car_idModel = tblModel.idModel
  where MakeD='Cat'
  AND ModelD ='320CL'
  AND YearManufacture='2004')
  
  where idStock='140038410'
  </code></pre>
  
  <p>While this doesn't work at all;</p>
  
  <pre><code>update tblStock t1
  set GuidePrice = (select 1.10 * AVG(Price)
  from tblStock t2
  where t2.Car_idMake = t1.Car_idMake
                  and t2.Car_idModel = t1.Car_idModel
                  and t2.YearManufacture = t1.YearManufacture )
  </code></pre>
  
  <p>where idStock='140038410'</p>