PHP & MySQL: Query to "sum" 2 rows from PHP

Question

I have the following table:

+------------+-------+------------+-----------+
| date       | time  | custom_id  | Number    |
+------------+-------+------------+-----------+
| 01/17/2014 | 00:00 | ABC123     | 15        |
| 01/17/2014 | 00:00 | HEY567     | 25        |
| 01/17/2014 | 00:30 | ABC123     | 17        |
| 01/17/2014 | 00:30 | HEY567     | 28        |
| 01/17/2014 | 01:00 | ABC123     | 17        |
| 01/17/2014 | 01:00 | HEY567     | 28        |
| 01/17/2014 | 01:30 | ABC123     | 17        |
| 01/17/2014 | 01:30 | HEY567     | 28        |
+------------+-------+------------+-----------+

How can I sum 2 rows (Number) with the same CUSTOM_ID & DATE and when the given time is only :30 minutes of difference round it to :00 and delete the :30? It should look like this in the end:

+------------+-------+------------+-----------+
| date       | time  | custom_id  | Number    |
+------------+-------+------------+-----------+
| 01/17/2014 | 00:00 | ABC123     | 32        |
| 01/17/2014 | 00:00 | HEY567     | 53        |
| 01/17/2014 | 01:00 | ABC123     | 34        |
| 01/17/2014 | 01:00 | HEY567     | 56        |
+------------+-------+------------+-----------+

How can I accomplish this with PHP or possibly with a MySQL query?

SQLFiddle: http://sqlfiddle.com/#!2/cc529/1

it would be better if you put different dates into the example — user4035
– user4035, Commented Feb 9, 2014 at 19:14
why date is varchar and not date? And time is also varchar and not time? — user4035
– user4035, Commented Feb 9, 2014 at 19:17

user4035 · Accepted Answer · 2014-02-09 19:25:04Z

1

This should work

SELECT date, MIN(time) as time, custom_id, SUM(Number) as Number
FROM table
GROUP BY date, custom_id, HOUR(time)

edited Feb 9, 2014 at 19:25

user4035

24k11 gold badges69 silver badges101 bronze badges

answered Feb 9, 2014 at 19:23

rralcala

1333 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3288852 Over a year ago

WOW. I'm amazed how simple it is, THANKS ALOT you just saved my job! here's the final result if someone else needs it: sqlfiddle.com/#!2/913b4/1

Collectives™ on Stack Overflow

PHP & MySQL: Query to "sum" 2 rows from PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related