Hi Rhys
If you provide this as a bunch of CREATE TABLE & INSERT statements it'll be easier for us to look at your problem.
Couple of things...
When you do..
MariaDB [maildb]> select mail_out_anon,mail_out_auth,timeslice from domaincounters where domain_id=19 order by timeslice desc limit 24;
The first row is...
| 0 | 10 | 2015-06-22 16:00:00 |
But when you do this...
select mail_out_anon+mail_out_auth,timeslice from domaincounters where domain_id=19 order by timeslice desc limit 24;
You get ...
| 19 | 2015-06-22 16:00:00 |
What the discrepancy? Is this being updated in the meantime? Something is going on here. I see nothing wrong with the SQL provided here to indicate why the result would be wrong.
Yes, this is a live database with counters being constantly updated for the actual timeslice :-)
I think using a computed column here would simplify the semantics of your query...
Thank you, I'll look into this.
As I said provide some table create & insert statements and you'll get some better asnwers.
CREATE TABLE `domaincounters` ( `id` int(11) NOT NULL AUTO_INCREMENT, `domain_id` int(11) NOT NULL, `timeslice` datetime DEFAULT NULL, `bad_filename` int(11) DEFAULT '0', `ham` int(11) DEFAULT '0', `mail_in` int(11) DEFAULT '0', `mail_out_anon` int(11) DEFAULT '0', `mail_out_auth` int(11) DEFAULT '0', `out_bad_filename` int(11) DEFAULT '0', `skip` int(11) DEFAULT '0', `spam` int(11) DEFAULT '0', `virus` int(11) DEFAULT '0', `suspicious_chars` int(11) DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `domain_id` (`domain_id`,`timeslice`) ) ENGINE=InnoDB AUTO_INCREMENT=1557693 DEFAULT CHARSET=utf8 The insert/update query looks like this: $query = "INSERT INTO domaincounters set timeslice = DATE_FORMAT(NOW(),'%Y-%m-%d %H:00:00'), $counter = $count, domain_id = " . $domainid->{'id'} . " ON DUPLICATE KEY UPDATE $counter = $counter + $count"; $counter is the column to be updated $count is the number to add to the counter Mit freundlichen Grüssen Benoit Panizzon -- I m p r o W a r e A G - ______________________________________________________ Zurlindenstrasse 29 Tel +41 61 826 93 07 CH-4133 Pratteln Fax +41 61 826 93 02 Schweiz Web http://www.imp.ch ______________________________________________________