How to reference previous row data via MySQL to produce cumlative percentage?  Need to reproduce Excel spreadsheet creating Pareto Chart using MySQL query, stored procedure, or cursor function.

Posted on 2011-10-13
Last Modified: 2012-05-12
I'm trying to reproduce the attached Excel spreadsheet designed to create a Pareto Chart using MySQL. ExcelParetoChart.xls

The problem I'm running into is column 4, which-- for example-- in row D6 is a simple formula of =D5+C6

In other words I need to reference data from a previous row in order to dynamically calculate the current row data.... Formula in Excel I'm try to reproduce in MySQL
I've been able to recreate the first three columns using the following query...
SELECT a.County, (a.`1/2010` + a.`2/2010` + a.`3/2010` + a.`4/2010` + a.`5/2010` + a.`6/2010`) AS `Sales`, 
      (SELECT (b.`1/2010` + b.`2/2010` + b.`3/2010` + b.`4/2010` + b.`5/2010` + b.`6/2010`) FROM `sales_county` b WHERE b.County = a.County)
      (SELECT SUM( c.`1/2010` + c.`2/2010` + c.`3/2010` + c.`4/2010` + c.`5/2010` + c.`6/2010`) FROM `sales_county` c)
    *100, 1
  ) AS `% of Total`
FROM `sales_county` a 

Open in new window

It seems I need a cursor function or stored procedure to do this properly.  

Here is the MySQL script to create the demo table and populate with demo data...

Thanks in advance!!

Question by:Giovanni Heward
    LVL 14

    Author Comment

    by:Giovanni Heward
    FYI--- here is what the Pareto chart looks like using the example data.
     Example Pareto chart using demo data...
    LVL 14

    Author Comment

    by:Giovanni Heward
    I figured this out using SQL variables; here is the final code...

    SET @cumlative=0;
    SELECT a.County, @sales := (a.`1/2010` + a.`2/2010` + a.`3/2010` + a.`4/2010` + a.`5/2010` + a.`6/2010` + a.`7/2010` + a.`8/2010` + a.`9/2010` + a.`10/2010` + a.`11/2010` + a.`12/2010`) AS `Sales`, 
    	@total := ROUND((@sales)/
    		(SELECT @total := SUM(c.`1/2010` + c.`2/2010` + c.`3/2010` + c.`4/2010` + c.`5/2010` + c.`6/2010` + c.`7/2010` + c.`8/2010` + c.`9/2010` + c.`10/2010` + c.`11/2010` + c.`12/2010`) FROM `new_mv_sales_buyer_county` c)
     		*100,1) as `% of Total`,
    	@cumlative := (@total + @cumlative) as `Cumlative %`
    FROM `new_mv_sales_buyer_county` a
    ORDER BY `Sales` DESC;

    Open in new window

    LVL 14

    Accepted Solution

    Here would be the actual working query for the example code provided above...

    SET @cumlative=0;
    SELECT a.County, @sales := ( a.`1/2010` + a.`2/2010` + a.`3/2010` + a.`4/2010` + a.`5/2010` + a.`6/2010` ) AS `Sales`,
         @total := ROUND( (@sales) / (
                   SELECT @total := SUM( b.`1/2010` + b.`2/2010` + b.`3/2010` + b.`4/2010` + b.`5/2010` + b.`6/2010` ) FROM `sales_county` b )
              *100, 1) AS `% of Total`,
         @cumlative := ( @total + @cumlative ) AS `Cumlative %`
    FROM `sales_county` a
    ORDER BY `Sales` DESC;

    Open in new window

    LVL 14

    Author Closing Comment

    by:Giovanni Heward
    Resolved my own issue...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    Title # Comments Views Activity
    Mysql vs Oracle 10 103
    Does SQL change much? 4 29
    MySQL 11 46
    Saving position of jquery sortable portlet to database 11 35
    Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API ( …
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now