maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #02688
Re: 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>:
> 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
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help : https://help.launchpad.net/ListHelp
>
Follow ups
References
