CODEWITHSUNDEEP
phpmysqldatetimesum
Tomek
Tomek

Reputation: 100

PHP MySQL, sum of rows based on datetime row

I'm trying to sum number of persons based on datetime rows.

this is how my table looks like:

id | date              | person |
---+-------------------+--------+
1  |2013-12-26 00:00:00|  3     |
---+-------------------+--------+    
2  |2013-12-26 00:00:00|  2     |
---+-------------------+--------+ 
3  |2013-12-26 00:00:00|  3     |
---+-------------------+--------+ 
4  |2018-10-21 00:00:00|  3     |
---+-------------------+--------+

What i want my query to do is: Sum all persons based on date. But i think my problem is not query it self (or maybe it is), but the datetime. I have something like

$date = "26.12.2013";
$date = strtotime(date("d.m.Y", strtotime($date)));
$date = date("Y-m-d", $date); 
$query= "SELECT '$date', SUM('person') totalperson FROM table_name WHERE date='$date' GROUP BY '$date'";

but the number returned is 2016 and expected is 8 :)

I hope my question is clear enough.

Upvotes: 0

Views: 1481

Answers (3)

buzdyk
buzdyk

Reputation: 622

What db engine do you use?

Anyway, this should work:

$date = "2013-12-26 00:00:00";
$query = "
  SELECT date, SUM(person) totalperson 
  FROM table_name WHERE date='$date' 
  GROUP BY date
";

Upvotes: 0

Mihai
Mihai

Reputation: 26784

SUM(`person`)

Use backticks for column name,you are suming a string

Upvotes: 4

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

SELECT 
    date, 
    SUM(person) totalperson 
FROM table_name 
WHERE DATE(date) = '$date' 
GROUP BY date

Use DATE() function

you are providing date string in date time column

Upvotes: 0

Related Questions