[SOLVED] Why SUM of column values return 1?

Why SUM of column values return 1?

I m trying to get sum of user's visits for current month. This is my code (if important to know,I use ORM RedBeanPHP):

$summ = R::exec('SELECT SUM(amount) FROM statistic WHERE type = "usuall_visit" AND month = :month',[':month'=>date('m')]);

And $summ is always 1, even if its not true. ._. My table statistic: https://drive.google.com/open?id=1MFt-FtEtqkk7QWQC2oAtMBA0WAgOIV4h

P.S. Also, when I try get values only of row with max value of column I need,like this:

$summ = R::exec('SELECT MAX(amount),value FROM statistic WHERE type = "usuall_visit" AND month = :month',[':month'=>date('m')]);

I run into error: "Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'somesite.statistic.value'; this is incompatible with sql_mode=only_full_group_by" I said ok, and tried add GROUP BY ID. But then I get just 1st row, but not only with actuall max value ._. I dunno what I did wrong

Solution

In RedBeanPHP, you must use other function than R::exec for SELECT queries. The number you are getting is probably number of rows returned by select or a simple value indicating success.

Try

$summ = (int)R::getCell('SELECT SUM(amount) FROM statistic WHERE type = "usuall_visit" AND month = :month',[':month'=>date('m')]);

For the second question, you might want to study SQL a bit, at least the very basics. Nested query like this may work for you.

SELECT value FROM statistic WHERE amount = (SELECT MAX(amount) FROM statistic WHERE type = "usuall_visit" AND month = :month') LIMIT 1

You will need to use it with R::getCell again (or R::getRow if you wanted to retrieve multiple columns).