CODEWITHSUNDEEP
sqlsql-server-2005
Perplexed
Perplexed

Reputation: 877

sql query to calculate monthly growth percentage

I need to build a query with 4 columns (sql 2005).

Column1: Product
Column2: Units sold
Column3: Growth from previous month (in %)
Column4: Growth from same month last year (in %)

In my table the year and months have custom integer values. For example, the most current month is 146 - but also the table has a year (eg 2011) column and month (eg 7) column.

Is it possible to get this done in one query or do i need to start employing temp tables etc??

Appreciate any help.

thanks,

KS

Upvotes: 1

Views: 11217

Answers (3)

Brian Hoover
Brian Hoover

Reputation: 7991

KS, To do this on the fly, you could use subqueries.

SELECT product, this_month.units_sold,
    (this_month.sales-last_month.sales)*100/last_month.sales,
    (this_month.sales-last_year.sales)*100/last_year.sales
    FROM (SELECT product, SUM(units_sold) AS units_sold, SUM(sales) AS sales
            FROM product WHERE month = 146 GROUP BY product) AS this_month,
         (SELECT product, SUM(units_sold) AS units_sold, SUM(sales) AS sales
            FROM product WHERE month = 145 GROUP BY product) AS last_month,
         (SELECT product, SUM(units_sold) AS units_sold, SUM(sales) AS sales
            FROM product WHERE month = 134 GROUP BY product) AS this_year
    WHERE this_month.product = last_month.product
      AND this_month.product = last_year.product

If there's a case where a product was sold in one month but not another month, you will have to do a left join and check for null values, especially if last_month.sales or last_year.sales is 0.

Upvotes: 1

topchef
topchef

Reputation: 19793

I am slightly guessing as the structure of the table provided is the result table, right? You will need to do self-join on month-to-previous-month basis:

SELECT <growth computation here> 
  FROM SALES s1 LEFT JOIN SALES s2 ON (s1.month = s2.month-1) -- last month join
                LEFT JOIN SALES s3 ON (s1.month = s3.month - 12) -- lat year join

where <growth computation here> looks like

((s1.sales - s2.sales)/s2.sales * 100), 
((s1.sales - s3.sales)/s3.sales * 100)

I use LEFT JOIN for months that have no previous months. Change your join conditions based on actual relations in month/year columns.

Upvotes: 1

CristiC
CristiC

Reputation: 22698

I hope I got them all:

SELECT
  Current_Month.product_name, units_sold_current_month,
  units_sold_last_month * 100 / units_sold_current_month prc_last_month,
  units_sold_last_year * 100 / units_sold_current_month prc_last_year
FROM
  (SELECT product_id, product_name, sum(units_sold) units_sold_current_month FROM MyTable WHERE YEAR = 2011 AND MONTH = 7) Current_Month
  JOIN
  (SELECT product_id, product_name, sum(units_sold) units_sold_last_month FROM MyTable WHERE YEAR = 2011 AND MONTH = 6) Last_Month
  ON Current_Month.product_id = Last_Month.product_id
  JOIN 
  (SELECT product_id, product_name, sum(units_sold) units_sold_last_year FROM MyTable   WHERE YEAR = 2010 AND MONTH = 7) Last_Year
  ON Current_Month.product_id = Last_Year.product_id

Upvotes: 1

Related Questions