maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #02690
Re: Bug in MAX() function?
“you have to use GROUP BY for it to work as expected.”
Not in this case you don’t. There’s no value in the SELECT to group by.
From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=tradingscreen.com@xxxxxxxxxxxxxxxxxxx] On Behalf Of Guillaume Lefranc
Sent: 19 June 2015 10:03
To: Benoit Panizzon
Cc: maria-discuss@xxxxxxxxxxxxxxxxxxx
Subject: Re: [Maria-discuss] Bug in MAX() function?
Hi Benoit,
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;
Hope this helps
-Guillaume
2015-06-19 10:59 GMT+02:00 Benoit Panizzon <benoit.panizzon@xxxxxx<mailto:benoit.panizzon@xxxxxx>>:
Hi all
I stumbled over that problem, while trying to graph mail traffic..
I have two different counters for emails sent with authentication and without.
Aggregated per hour.
I would like to total the both values per row, to create a graph of outgoing
emails per hour.
MariaDB [maildb]> select mail_out_anon,mail_out_auth from domaincounters;
+---------------+---------------+
| mail_out_anon | mail_out_auth |
+---------------+---------------+
| 8 | 58 |
| 8 | 48 |
| 4 | 63 |
| 9 | 53 |
| 2 | 36 |
| 0 | 12 |
| 3 | 2 |
| 0 | 2 |
| 0 | 6 |
| 0 | 2 |
| 0 | 9 |
| 0 | 44 |
| 14 | 63 |
| 0 | 96 |
| 7 | 43 |
| 4 | 61 |
| 2 | 43 |
| 2 | 66 |
| 0 | 86 |
| 6 | 77 |
| 6 | 55 |
| 0 | 63 |
| 6 | 48 |
| 2 | 52 |
+---------------+---------------+
> select mail_out_anon+mail_out_auth as mail_out_total from domaincounters;
+-----------------------------+
| mail_out_anon+mail_out_auth |
+-----------------------------+
| 70 |
| 56 |
| 67 |
| 62 |
| 38 |
| 12 |
| 5 |
| 2 |
| 6 |
| 2 |
| 9 |
| 44 |
| 77 |
| 96 |
| 50 |
| 65 |
| 45 |
| 68 |
| 86 |
| 83 |
| 61 |
| 63 |
| 54 |
| 54 |
+-----------------------------+
Now I need the max value of that addition to auto-scale the graph.
MariaDB [maildb]> select MAX(mail_out_anon+mail_out_auth)
+----------------------------------+
| MAX(mail_out_anon+mail_out_auth) |
+----------------------------------+
| 187 |
+----------------------------------+
What did MariaDB count here? I was expecting a value 96 which is the largest
value of all the additions.
Also trying various ways of first using GREATEST() always returns a way to
high value.
Is it a bug, or did I do something wrong?
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
______________________________________________________
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@xxxxxxxxxxxxxxxxxxx<mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
References
