SUM multiple rows in MySQL to display on ChartJS

I am trying to SUM all the values in my MYSQL database based on date (month) to display on chartJS. I have multiple rows consisting of recyclable materials. Inside those rows are multiple columns consisting of dates and weights captured daily by staff based on those materials in each column. My PHP code for ChartJS is as follows:

My SQL query is currently:

// connection
$db_conx = mysqli_connect("localhost", "ecemsmaster", "ecemsmaster", "ecemsmaster");
// Evaluate the connection

if (mysqli_connect_errno()) {
    echo mysqli_connect_error("Our database server is currently down. : (");
// Initialize Variables
    $months = '';
    $subgrades = '';
    $castaluminiums = '';
    $coppers = '';
    $stainlesssteels = '';
    $plastics = '';
    $batteriess = '';
    $brasss = '';
    $cabless = '';
    $dates = '';
// Get lists from database

$sql = mysqli_query($db_conx, "SELECT * FROM daily_recyclables");
while($row = mysqli_fetch_array($sql)){
    $subgrade = $row['subgrade'];
    $castaluminium = $row['castaluminium'];
    $copper = $row['copper'];
    $stainlesssteel = $row['stainlesssteel'];
    $plastic = $row['plastic'];
    $batteries = $row['batteries'];
    $brass = $row['brass'];
    $cables = $row['cables'];
    $date = date('M, Y', strtotime($row['date']));

    $dates = $dates.'"'.$date.'",';
    $subgrades = $subgrades.$subgrade.',';
    $castaluminiums = $castaluminiums.$castaluminium.',';
    $coppers = $coppers.$copper.',';
    $stainlesssteels = $stainlesssteels.$stainlesssteel.',';
    $plastics = $plastics.$plastic.',';
    $batteriess = $batteriess.$batteries.',';
    $brasss = $brasss.$brass.',';
    $cabless = $cabless.$cables.',';
$dates = trim($dates, ",");
$subgrades = trim($subgrades, ",");
$castaluminiums = trim($castaluminiums, ",");
$coppers = trim($coppers, ",");
$stainlesssteels = trim($stainlesssteels, ",");
$plastics = trim($plastics, ",");
$batteriess = trim($batteriess, ",");
$brasss = trim($brasss, ",");
$cabless = trim($cabless, ",");


Then of course my JS and HTML code.

What the code is currently doing is showing dates as months like expected, but what I didn’t expect is multiple months (Aug, Aug, Aug) to display on the chart. So as you can imagine, 30 days of capturing data, there are 30 Aug, Aug, Aug on my chart instead of ONE total for Aug.

How do I use the SUM function correctly in my MySQL query to make multiple entries for Aug, show up as ONE total for that particular material?


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I changed the MySQLi query to this:

$sql = mysqli_query($db_conx, "SELECT MONTHNAME(date) AS date, SUM(subgrade) AS subgrade, SUM(castaluminium) AS castaluminium, SUM(copper) AS copper, SUM(stainlesssteel) AS stainlesssteel, SUM(plastic) AS plastic, SUM(batteries) AS batteries, SUM(brass) AS brass, SUM(cables) AS cables FROM daily_recyclables GROUP BY MONTHNAME(date)");

And it gave me the output I was looking for.

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x