maria-discuss team mailing list archive

[Benoit Panizzon <benoit.panizzon@xxxxxx>]

Hi Guillaume and the others who answered.

Thank you.

> MAX() is an aggregate function, you have to use GROUP BY for it to work as
> expected.
> e.g. select MAX(mail_out_anon+mail_out_auth) FROM domaincounters GROUP BY
> timefunction;

Nope, it's not doing what I want when I GROUP the entries.

Ok, here's real data from my table:

The counters for the last 24 hours for the domain with ID 19:

MariaDB [maildb]> select mail_out_anon,mail_out_auth,timeslice from 
domaincounters where domain_id=19 order by timeslice desc limit 24;
+---------------+---------------+---------------------+
| mail_out_anon | mail_out_auth | timeslice           |
+---------------+---------------+---------------------+
|             0 |            10 | 2015-06-22 16:00:00 |
|            45 |           332 | 2015-06-22 15:00:00 |
|            28 |           560 | 2015-06-22 14:00:00 |
|            16 |           434 | 2015-06-22 13:00:00 |
|             4 |           291 | 2015-06-22 12:00:00 |
|            43 |           372 | 2015-06-22 11:00:00 |
|            35 |           345 | 2015-06-22 10:00:00 |
|            12 |           397 | 2015-06-22 09:00:00 |
|            15 |           400 | 2015-06-22 08:00:00 |
|            10 |           301 | 2015-06-22 07:00:00 |
|            12 |            83 | 2015-06-22 06:00:00 |
|             0 |            45 | 2015-06-22 05:00:00 |
|             0 |            14 | 2015-06-22 04:00:00 |
|             0 |            10 | 2015-06-22 03:00:00 |
|             0 |            29 | 2015-06-22 02:00:00 |
|             0 |            29 | 2015-06-22 01:00:00 |
|             6 |           111 | 2015-06-22 00:00:00 |
|             4 |           119 | 2015-06-21 23:00:00 |
|            23 |           294 | 2015-06-21 22:00:00 |
|            25 |           356 | 2015-06-21 21:00:00 |
|            18 |           270 | 2015-06-21 20:00:00 |
|            12 |           314 | 2015-06-21 19:00:00 |
|            16 |           338 | 2015-06-21 18:00:00 |
|            31 |           250 | 2015-06-21 17:00:00 |
+---------------+---------------+---------------------+

I want to do one graph 'mail_out' and use the sum of both colums per time:

MariaDB [maildb]> select mail_out_anon+mail_out_auth,timeslice from 
domaincounters where domain_id=19 order by timeslice desc limit 24; 
+-----------------------------+---------------------+
| mail_out_anon+mail_out_auth | timeslice           |
+-----------------------------+---------------------+
|                          19 | 2015-06-22 16:00:00 |
|                         377 | 2015-06-22 15:00:00 |
|                         588 | 2015-06-22 14:00:00 |
|                         450 | 2015-06-22 13:00:00 |
|                         295 | 2015-06-22 12:00:00 |
|                         415 | 2015-06-22 11:00:00 |
|                         380 | 2015-06-22 10:00:00 |
|                         409 | 2015-06-22 09:00:00 |
|                         415 | 2015-06-22 08:00:00 |
|                         311 | 2015-06-22 07:00:00 |
|                          95 | 2015-06-22 06:00:00 |
|                          45 | 2015-06-22 05:00:00 |
|                          14 | 2015-06-22 04:00:00 |
|                          10 | 2015-06-22 03:00:00 |
|                          29 | 2015-06-22 02:00:00 |
|                          29 | 2015-06-22 01:00:00 |
|                         117 | 2015-06-22 00:00:00 |
|                         123 | 2015-06-21 23:00:00 |
|                         317 | 2015-06-21 22:00:00 |
|                         381 | 2015-06-21 21:00:00 |
|                         288 | 2015-06-21 20:00:00 |
|                         326 | 2015-06-21 19:00:00 |
|                         354 | 2015-06-21 18:00:00 |
|                         281 | 2015-06-21 17:00:00 |
+-----------------------------+---------------------+

Ok, so that is the sum I want to graph. To correctly scale that graph, I need 
the largest value of those 24 entries.

That would be 588 occuring at 2015-06-22 14:00:00

select max(mail_out_anon+mail_out_auth),timeslice from domaincounters where 
domain_id=19 order by timeslice desc limit 24;
+----------------------------------+---------------------+
| max(mail_out_anon+mail_out_auth) | timeslice           |
+----------------------------------+---------------------+
|                              656 | 2015-06-15 13:00:00 |
+----------------------------------+---------------------+

Nope, wrong value...

select max(mail_out_anon+mail_out_auth),timeslice from domaincounters where 
domain_id=19 group by timeslice order by timeslice desc limit 24;
+----------------------------------+---------------------+
| max(mail_out_anon+mail_out_auth) | timeslice           |
+----------------------------------+---------------------+
|                               31 | 2015-06-22 16:00:00 |
|                              377 | 2015-06-22 15:00:00 |
|                              588 | 2015-06-22 14:00:00 |
|                              450 | 2015-06-22 13:00:00 |
[...]

Nope, I want just one value, the largest...

So where do I make the mistake?

Hmm. I think I see the problem now... the MAX() is done before sorting and 
limiting the result, right?

So I can't use limit but I have to use a where clause to select timeslices 
after a specific time.

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
______________________________________________________