• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1163
  • Last Modified:

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.

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`, 
  ROUND( 
      (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 
ORDER BY `Sales` DESC;

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...
sample-data.sql

Thanks in advance!!


0
Giovanni Heward
Asked:
Giovanni Heward
  • 4
1 Solution
 
Giovanni HewardAuthor Commented:
FYI--- here is what the Pareto chart looks like using the example data.
 Example Pareto chart using demo data...
0
 
Giovanni HewardAuthor Commented:
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

0
 
Giovanni HewardAuthor Commented:
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

0
 
Giovanni HewardAuthor Commented:
Resolved my own issue...
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now