UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Pivoting Data    
 
   
KantWin
post Dec 26 2015, 09:24 PM
Post#1



Posts: 581
Joined: 1-August 02
From: Alabama


I have a MySQL database that I'm using to track bicycle rides. It may be overkill, but it is what it is...

I have the following columns -
RideDate
RideDistance
RideTime
RideBike

What I would like to do is pivot the data, and summarize by year.
In Excel, I had a pivot table (attachment). I'd like to replicate that using MySQL, so I can easily view it on a web page.

I just can't seem to make it work. Any help would be appreciated.
Attached File(s)
Attached File  Capture234.JPG ( 29.93K )Number of downloads: 1
 
Go to the top of the page
 
cheekybuddha
post Dec 28 2015, 06:57 PM
Post#2


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


Hi,

Happy Festive Season!

You haven't shown us the structure of your table. When asking help about MySQL it's useful to post the CREATE TABLE statements for your tables.

One way to do this in MySQL is to use a Stored Procedure to create and execute the SQL of a query returning the data you seek.

I'm assuming your table is called `rides` and has the fields you described (plus hopefully a PK column too!)

The SQL for the query you want to end up with will be something like:
CODE
SELECT
  YEAR(r.RideDate) 'Year',
  SUM(IF(r.RideBike = 'Mountain', r.RideDistance, 0)) `Mountain`,
  SUM(IF(r.RideBike = 'Tourist', r.RideDistance, 0)) `Tourist`,
  SUM(IF(r.RideBike = 'Trek 1.1', r.RideDistance, 0)) `Trek 1.1`,
  SUM(IF(r.RideBike = 'Trek SS', r.RideDistance, 0)) `Trek SS`,
  SUM(r.RideDistance) `Total`
FROM rides r
GROUP BY YEAR(r.RideDate) WITH ROLLUP
-- ORDER BY YEAR(r.RideDate)
;

WITH ROLLUP will give you totals as the last row in the query returned, but means you can not specify an ORDER BY clause.

However, writing this query is painful because it requires you to manually list out each possible RideBike manually in the SUM statements which is not really practical, especially as you will have to remember to update the query each time you add as new bike.

This is where the Stored Procedure comes in:
CODE
DELIMITER $$
CREATE PROCEDURE `rides_pivot`()
BEGIN

  DECLARE SQLStmt TEXT;
  DECLARE bike    VARCHAR(100);
  DECLARE bikes   VARCHAR(255);
  DECLARE done    INT DEFAULT FALSE;
  DECLARE rs      CURSOR FOR SELECT DISTINCT RideBike FROM rides ORDER BY RideBike;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  OPEN rs;
  read_loop: LOOP
    FETCH rs INTO bike;
    IF done THEN
      LEAVE read_loop;
    ELSE
      SET bike = CONCAT('SUM(IF(r.RideBike = \'', bike, '\', r.RideDistance, 0)) `', bike,'`');
      SET bikes = CONCAT_WS(', ', bikes, bike);
    END IF;
  END LOOP;
  CLOSE rs;
  SET @SQL = CONCAT (
               'SELECT
                YEAR(r.RideDate) \'Year\',',
               bikes, ', ',
               'SUM(r.RideDistance) `Total`
                FROM rides r
                GROUP BY YEAR(r.RideDate) WITH ROLLUP'
             );
  PREPARE SQLStmt FROM @SQL;
  EXECUTE SQLStmt;
  DEALLOCATE PREPARE SQLStmt;
  
END$$
DELIMITER;

The procedure opens a cursor (traditionally frowned upon, but if you have less than 1000 bikes, it won't harm you much!) to get a list of all the bikes.

It then created the SUM statements based on the bike and, once done, adds the rest of the query SQL.

Then it executes the statement (using PREPARE) and returns the results.

hth,

d
Go to the top of the page
 
cheekybuddha
post Dec 28 2015, 06:58 PM
Post#3


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


Forgot to add:

You call the Stored procedure with:
CODE
CALL rides_pivot();


hth,

d
Go to the top of the page
 
KantWin
post Dec 30 2015, 05:38 PM
Post#4



Posts: 581
Joined: 1-August 02
From: Alabama


Thank you for replying.

I'm on a shared resource server, and I don't think I can do it as a stored procedure.
I'd also like to make it dynamic, so that I don't have to mention the types of bikes in the construction of the query, but rather have it determine that, and then run the pivot.
Go to the top of the page
 
cheekybuddha
post Dec 30 2015, 08:12 PM
Post#5


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


Why should being on a shared resource server prevent you from using a stored procedure?
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 09:47 AM