Querying a MySQL database with Google Charts using a cumulative sum

Question

I have a MySQL table of timestamps which I'd like to output to a Google Chart, where the x-axis has the dates, and the y-axis has the cumulative "running total" of number of instances a particular timestamp occurs per day.

SELECT DATE(  `complete_date` ) AS ForDate, COUNT( * ) AS DayProgress
FROM  `completed_courses` 
WHERE complete_date
IN (
SELECT DISTINCT completed_courses.complete_date
FROM users
INNER JOIN completed_courses ON users.u_id = completed_courses.u_id
INNER JOIN wg_courses ON wg_courses.c_id = completed_courses.c_id
INNER JOIN groups ON wg_courses.wg_id = groups.g_id
WHERE users.categories LIKE  '4h%'
)
GROUP BY DATE(complete_date) 
ORDER BY ForDate

The above code will output ForDate as each individual date, and DayProgress as the number of timestamps for that day. I'm trying to implement a running total using SET @runningTotal = 0, but I'm not sure where to start since the query is already complex. Also, I'm wondering if there's a more efficient way to run the query, as there is sometimes a gateway timeout when running it.

EDIT 2:

Actually, I decided to use RADAR's sql query, but the chart isn't showing up, and is giving a "Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given" error, even though the query is executing correctly. Here is the mysql output:

ForDate   ||   RunningTotal
2014-07-29  |   950
2014-07-30  |   3063
2014-07-31  |   3669
2014-08-01  |   4584
2014-08-02  |   5088
etc...

Here is the PHP code:

$sth = mysqli_query($con, "
SET @runningTotal := 0
SELECT T.ForDate,  (@runningTotal := @runningTotal + T.DayProgress) as RunningTotal
FROM
(

    SELECT DATE(  completed_courses.complete_date ) AS ForDate, COUNT( * ) AS DayProgress
    FROM users
    INNER JOIN completed_courses ON users.u_id = completed_courses.u_id
    INNER JOIN wg_courses ON wg_courses.c_id = completed_courses.c_id
    INNER JOIN groups ON wg_courses.wg_id = groups.g_id
    WHERE users.categories LIKE  '4h%'
    GROUP BY DATE(completed_courses.complete_date)
) T
ORDER BY T.ForDate
");

$rows = array();
//flag is not needed
$flag = true;
$table = array();
$table['cols'] = array(

    // Labels for your chart, these represent the column titles
    // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
    array('label' => 'ForDate', 'type' => 'string'),
    array('label' => 'RunningTotal', 'type' => 'number')

);

$rows = array();
while($r = mysqli_fetch_assoc($sth)) {


  echo $r['ForDate'];

    $temp = array();
    // the following line will be used to slice the Pie chart
    $temp[] = array('v' => (string) $r['ForDate']); 

    // Values of each slice
    $temp[] = array('v' => (int) $r['RunningTotal']); 
    $rows[] = array('c' => $temp);
}

$table['rows'] = $rows;
$jsonTable = json_encode($table);

It's interesting to note that even though the query works, there is an error when converting to JSON and then outputting to Google Charts.

radar · Answer 1 · 2014-10-16T03:24:26.517

you can have mysql variable holding the running total

Also the IN clause is not needed, as you can directly do group by on existing dates

set @runningTotal := 0
SELECT T.ForDate,  (@runningTotal := @runningTotal + T.DayProgress) as RunningTotal
FROM
(

    SELECT DATE(  completed_courses.complete_date ) AS ForDate, COUNT( * ) AS DayProgress
    FROM users
    INNER JOIN completed_courses ON users.u_id = completed_courses.u_id
    INNER JOIN wg_courses ON wg_courses.c_id = completed_courses.c_id
    INNER JOIN groups ON wg_courses.wg_id = groups.g_id
    WHERE users.categories LIKE  '4h%'
    GROUP BY DATE(completed_courses.complete_date)
) T
ORDER BY T.ForDate

+ you need the order clause: ORDER BY ForDate – Paul Maxwell Oct 16 '14 at 03:22 — Paul Maxwell, Oct 16 '14 at 03:22

Querying a MySQL database with Google Charts using a cumulative sum

EDIT 2:

1 Answers1