SQL Statement Doubt

[FunkyDexter]

I think something like this would do it:-

SELECT reference, month(valdate), 
   (SELECT AVG(prod_data.val) * SUM(shares) AS MonthlyAVG
   FROM prod_data
   INNER JOIN pos_data
      ON MONTH(valdate) = MONTH(period)
      AND YEAR(valdate) = YEAR(period)
   WHERE YEAR(valdate) = '2006' AND MONTH(valdate)BETWEEN 1 AND 3
   GROUP BY MONTH(prod_data.valdate))
GROUP BY reference, month(valdate)

Note the Group By on the inner select is a logically redundant because it's also covered by the Group By on the outer select. It should improve performance by limiting the number of rows returned by the inner select. that said, it might actually reduce performance by intrducing an extra operation so try it with and without to see which performs better.

[FunkyDexter]

Actually, I lied, it's not redundant because it's controlling the values returned by the aggregate functions. Also, the query would look better written like this:-

SELECT reference, month(valdate), 
   (SELECT AVG(prod_data.val) * SUM(shares)
   FROM prod_data
   INNER JOIN pos_data
      ON MONTH(valdate) = MONTH(period)
      AND YEAR(valdate) = YEAR(period)
   WHERE YEAR(valdate) = '2006' AND MONTH(valdate)BETWEEN 1 AND 3
   GROUP BY MONTH(valdate)) as monthly_value
GROUP BY reference, month(valdate)