Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    3

    Unanswered: creating new variables / pivots

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.State
         , t.Date
         , t.Sales
         , a.avgsales AS Sales_Index_Denominator
      FROM ( SELECT State
                  , EXTRACT(YEAR_MONTH FROM Date) AS yyyymm
                  , AVG(Sales) AS avgsales
               FROM Sales1 
             GROUP 
                 BY State
                  , EXTRACT(YEAR_MONTH FROM Date) ) AS a
    INNER
      JOIN Sales1 AS t
        ON t.State = a.State
       AND EXTRACT(YEAR_MONTH FROM t.Date) = a.yyyymm
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2012
    Posts
    3
    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!
    Last edited by analytictype; 01-08-12 at 13:15.

  4. #4
    Join Date
    Jan 2012
    Posts
    3
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by analytictype View Post
    when we alias avg sales as Denominator, why can't we use the alias in the calculation on the next line?
    because you're not allowed to
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •