SUM multiple rows in MySQL to display on ChartJS

Question

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:

<?php
ob_start();
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// 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. : (");
    exit();
}
// 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?

I have explained it before downvoting. Also, why do you care about the votes? That is not important at the moment. Focus on improving the question as much as you can. Make sure you are providing [mcve] and you are focusing on a single technology. What exactly are you having problem with now? HTML, PHP, chart.js, mysqli or something else? Please clarify your question and chances are people will find it more useful and will upvote it. — Dharman, Oct 17 '21 at 20:47
mysqli is just a PHP extension for executing SQL queries on MySQL server. It seems that your question has nothing to do with mysqli really. — Dharman, Oct 17 '21 at 21:15
I understand. But i have found a solution that works for me. Even though it's working, im not sure if it's the correct way to use the SUM function. — masterofmatrix, Oct 17 '21 at 21:19
Do these answers help? https://stackoverflow.com/questions/937652/mysql-select-sum-group-by-date https://stackoverflow.com/questions/13152796/mysql-query-getting-totals-by-month https://stackoverflow.com/questions/49155073/mysql-cumulative-sum-and-group-by-month-year https://stackoverflow.com/questions/15245229/mysql-how-to-do-a-sum-by-product-and-by-month https://stackoverflow.com/questions/49604180/mysql-sum-amount-by-each-months-from-last-12-month-to-this-month — Dharman, Oct 17 '21 at 21:21

score 0 · Accepted Answer · edited Oct 17 '21 at 21:18

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.

SUM multiple rows in MySQL to display on ChartJS

1 Answers1