mysql array selecting and sum values

Question

I own an array that three records containing:

value     datetime
2       03/03/2015 14:34:00
4       03/03/2015 14:36:00
5       03/03/2015 13:34:00

I want to select the records that are on time 14 and sum them. In the above example would be 4 + 2 = 6

How can I do this?

$sql ="SELECT amperagem, data FROM tomada WHERE date(data) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";// 
mysql_select_db('localiza');
$retval = mysql_query( $sql, $conn );
$num_rows = mysql_num_rows($retval);
while($row = mysql_fetch_array($retval, MYSQL_BOTH)){    
$hour= substr(($row['data']),11, 2);

how does your table definition look like? do you want to sum all values at a certain hour, regardless the day (date) ? I mean, time 14:xx:xx can occur in your table many time on different days all year. — MrSimpleMind, Mar 10 '15 at 15:08

vyncjob · Answer 1 · 2015-03-10T14:57:32.667

0

You can actually get yourself the value through a single SQL query:

Be sure to read up on http://www.w3schools.com/sql/func_datepart.asp

So to get all records with a datetime containing 14:XX:XX

SELECT value FROM tomada WHERE HOUR(data) = 14;

Now simply get the rows like you did and retrieve the 'value' per row and add them up

$res = mysql_query( $sql, $conn );
$returnObjects = array();
        if ($res == null) {
            return $returnObjects;      
        }       
        while(!is_null($row = mysql_fetch_row($res))){
            $returnObjects[] = $row;
        }
$returnArray = mysql_fetch_array($returnObjects);
$sum = 0;
for($row = 0, $size = count($returnArray); $row < $size; $row++){
        $sum += $returnArray[$row][0]; //Note 0 is the value you need
}
return $sum;

Note it can be done in less lines of codes with less steps but I find this helps reading what i'm doing. Also some additional checks if certain objects are NULL or values are invalid is recommended.

edited Mar 10 '15 at 14:57

answered Mar 10 '15 at 13:50

vyncjob

21
5

Did you try to run the SQL in your PhpMyAdmin? Hmm I can check the SQL myself. Also check if you need mysql OR mysqli. – vyncjob Mar 10 '15 at 14:15
http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_month Found at: http://stackoverflow.com/questions/13402012/select-row-by-datepart Fixed the main answer for you, should work now. Works for me in PhpMyAdmin – vyncjob Mar 10 '15 at 14:19
I found that line but don't works too, you know where is the problem? $sql = "SELECT SUM(amperagem) AS soma, HOUR(data) as hora FROM tomada WHERE data >='15:00:00' AND data <= '15:59:59'"; – Roberto Bahia Mar 10 '15 at 14:36
When you SELECT SUM as soma, you get a different value to extract. SELECT value FROM tomada WHERE HOUR(data) = 15; this should be what you want – vyncjob Mar 10 '15 at 14:58

score 0 · Answer 2 · answered Mar 10 '15 at 14:41

0

The sql is as simple as:

select sum(value) from tomada where hour(datetime) = 14;

answered Mar 10 '15 at 14:41

MrSimpleMind

7,890
3
40
45

mysql array selecting and sum values

2 Answers2