Alex Alex - 7 months ago 10
SQL Question

Add up monthly values base on definition on quarter

Trying to Add up monthly values base on definition on which month belongs to which quarter (so definition can be update):

enter image description here

I'm trying to use inner join to achieve but failed:

SELECT qr, sum(price)
INTO Table3
FROM Table1
INNER JOIN Table2
on Table1.mth = Table2.mth
INNER JOIN Table3
on Table2.qr = Table3.qr


Also I'm not sure if an additional column in Table1 (qr) will make things easier?

Answer

If your table3 already have those 4 rows to start with and you just want to update the prices in table3 to reflect the price of each quarter you can use this query:

UPDATE Table3 T3, 
  (SELECT T2.qr,sum(T1.price) as quarterly_price
   FROM Table1 T1
   INNER JOIN Table2 T2 ON T1.mth = T2.mth
   GROUP BY T2.qr) AS Result
SET T3.price = Result.quarterly_price
WHERE T3.qr = Result.qr;

sqlfiddle to Update Table3

If table3 doesn't have those 4 rows and you want to insert them, use this query:

INSERT INTO Table3
  SELECT T2.qr,sum(T1.price) as quarterly_price
   FROM Table1 T1
   INNER JOIN Table2 T2 ON T1.mth = T2.mth
   GROUP BY T2.qr;

sqlfiddle to Insert into Table3

EDIT: Tin I modify a little from your code and this works fine in Access's query:

SELECT Table2.qr,sum(Table1.price) as Price_new
INTO Table3
FROM Table1
INNER JOIN Table2 ON Table1.mth = Table2.mth
GROUP BY Table2.qr;