[X]My Assistant

[KantWin]

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)

 Capture234.JPG ( 29.93K )Number of downloads: 1

 

[cheekybuddha]

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

[cheekybuddha]

Forgot to add:

You call the Stored procedure with:

CODE

CALL rides_pivot();

hth,

d

[KantWin]

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.

[cheekybuddha]

Why should being on a shared resource server prevent you from using a stored procedure?