0
I have a system I used in 2013. I am migrating to the latest version of php and am having this error:
Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sitepara_bd.financ_despesa.data' which is not functionally dependent on Columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
EXAMPLE OF THE TIME: http://sqlfiddle.com/#! 9/28638/17
Code:
$year = $_GET["ano"]; //2013
$query_rs = "SELECT
$year AS Year,
m.month AS Month,
fr.SomaEntradaAtual AS Entry,
fd.SomaSaidaAtual AS Output,
IFNULL(fr.SomaEntradaAtual,0) - IFNULL(fd.SomaSaidaAtual,0) AS Balance
FROM (
SELECT 'Jan' AS MONTH
UNION SELECT 'Feb' AS MONTH
UNION SELECT 'Mar' AS MONTH
UNION SELECT 'Apr' AS MONTH
UNION SELECT 'May' AS MONTH
UNION SELECT 'Jun' AS MONTH
UNION SELECT 'Jul' AS MONTH
UNION SELECT 'Aug' AS MONTH
UNION SELECT 'Sep' AS MONTH
UNION SELECT 'Oct' AS MONTH
UNION SELECT 'Nov' AS MONTH
UNION SELECT 'Dec' AS MONTH
) AS m
LEFT JOIN (SELECT Month(DATA) AS rmonth, SUM(valor) AS SomaEntradaAtual,
DATA FROM financ_receita WHERE YEAR(DATA) = $year
GROUP BY MONTH(DATA)) AS fr
ON MONTH(STR_TO_DATE(CONCAT(m.month, ' $year'),'%M %Y')) = MONTH(fr.DATA)
LEFT JOIN (SELECT Month(DATA) AS dmonth, SUM(valor) AS SomaSaidaAtual,
DATA FROM financ_despesa WHERE YEAR(DATA) = $year
GROUP BY MONTH(DATA)) AS fd
ON MONTH(STR_TO_DATE(CONCAT(m.month, ' $year'),'%M %Y')) = MONTH(fd.DATA)";
By being a select above my knowledge, I had asked for help. Now again ask for help to hit this error.
I read in some places saying to deactivate the only_full_group_by, but doing so does not return any value:
Thank you
In your subselects you place the DATA field in the list of fields to be recovered but such field is not in the GROUP BY list. Older versions of Mysql accepted such a construction but newer versions may not accept it. This is your case where your DBMS is configured with the option: sql_mode=only_full_group_by. If you want the same behavior as before do not specify such clause since it is not DEFAULT in version 8.
– anonimo
@anonym as my code should be I do not know how to modify it, because who helped me at the time was a friend. He is no longer present. I had already disabled the
only_full_group_by, but when I do not appear any results in the table.– Tiago
See: https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html
– anonimo
@anonymo please help me in my code I am early trying to make it work. Just missing this part.
– Tiago
The solution I already said above, use the proper sql_mode, but the alternative is you remove the DATA field from the select-lists in your subselects.
– anonimo
But I need to sharpen the dates.
– Tiago
Then add the DATE field to the list of the GROUP BY clause.
– anonimo
@anonymity, but if I knew how to do this I would already have :( as it is a complex code, I’m lost. He is above my knowledge, all the other selects I have are simple, just this is complex.
– Tiago
@anonymo, please, give me that strength. I don’t know how to do this.
– Tiago
@Tiago, http://www.sqlfiddle.com/#! 9/28638/20
– bruno
@Thank you very much. Put here your answer for me to give score. Thank you very much!!! Seeing the result, I can see which is the correct form, thank you very much for your kindness!
– Tiago
@Bruno returned to development, and this error message appears in phpMyAdmin (https://prnt.sc/o6k8ic), this is the new link (http://www.sqlfiddle.com/#!9/ad487c/1). Can you help me again?
– Tiago