Imagine the small table created by the code below. My end goal is to have a new column of data that is equal to (sales in a given row)/(average of sales by state by month); basically a kind of index. Let's call this new variable sales_index.

So sales_index for the 1st row of data = 1, second row of data = 1000/1500 etc.

CREATE Table Sales1
(
State Varchar (2) Not Null,
Date Date Not Null,
Sales Varchar (20) Not Null
);

INSERT INTO Sales1 (State, Date, Sales) VALUES ('NY','2012-01-01',1000);

INSERT INTO Sales1 (State, Date, Sales) VALUES ('NY','2012-02-01',1000);

INSERT INTO Sales1 (State, Date, Sales) VALUES ('NY','2012-02-02',2000);

INSERT INTO Sales1 (State, Date, Sales) VALUES ('NJ','2012-01-01',1200);

INSERT INTO Sales1 (State, Date, Sales) VALUES ('NJ','2012-01-01',1100);

I'm kind of stuck. I can get the 1500 I would need for the denominator to be used to calculate sales_index for just the 2nd and 3rd rows of data using the code below, but not sure that I am heading down the right direction:

SELECT
State,
Avg(IF(Month(Date)=2,Sales,Null)) AS Sales_Index_Denominator
FROM Sales1
GROUP BY State;

I have been using MySQL for 1 day (haha) and am very much used to Excel functionality / pivots / formulas as a way of manipulating data. I'm not sure if I should do these calculations and save results in a different table, then join the tables etc. Any help is appreciated.

Hi-

First - thank you so very much! So I was at least guessing in the right direction in terms of needing to use a join . I want to make sure I understand what you are doing though, as your FROM clause is a bit more complex than Ive seen so far. You are using a subquery that in essence is constructing a new table which you are declaring "a", from which we are doing the inner join on, is this correct? My question is what happens to this table "a"? is it only temporary?When i use the SHOW TABLES FROM command, I don't see any table 'a' listed. Also, what this join is doing is sort of like concatenating state & month and merging together using that as a unique key.

I have some other questions - once I run this query and get the result set, what is the most efficient way to further manipulate the data? I'd like to calculate other things, like create new variables that see how far rows are from the average monthly values that we created, or create the monthly sales index. I rewrote your code like so (changes in bold):

CREATE Table Step1
SELECT t.State
, t.Date
, t.Sales
, a.avgsales AS Sales_Index_Denominator
FROM ( SELECT State
, EXTRACT(MONTH FROM Date) AS mm
, AVG(Sales) AS avgsales
FROM Sales1
GROUP
BY State
, mm ) AS a
INNER
JOIN Sales1 AS t
ON t.State = a.State
AND EXTRACT(MONTH FROM t.Date) = a.mm

CREATE TABLE Result
SELECT *,
Sales/Sales_Index_Denominator AS Sales_Index
FROM step1;


I appreciate your feedback as to whether this is an optimal way to handle this; as some datbases are quite large I am thinking there has to be a better method. I've ordered your book from Amazon, and it should arrive shortly!

I think I answered my own follow up question. I rewrote the top of the query:

CREATE Table IndexedSales
SELECT t.State
, t.Date
, t.Sales
, a.avgsales AS denominator
,g.UVs/a.avgsales AS sales_index

so my next question is, when we alias avg sales as Denominator, why can't we use the alias in the calculation on the next line? How does sql execute code? It looks like it isnt line by line, as it has to create table "a" first, then join it to calculate sales_index and then create the table indexedsales

because you're not allowed to