phpmyadmin Question posted in
The official documentation can be found here.

SQL Query Group by and order by – Phpmyadmin

My sql query is like

SELECT `speciesname`, sum(`total_species_catch`) as species_catch FROM `species_estimation` GROUP by `speciesid` ORDER by `species_catch` DESC LIMIT 0,10

It is working fine in localhost phpmyadmin

In server it shows error like 

SELECT list is not in GROUP BY clause and contains nonaggregated column 'fimsul.species_estimation.speciesname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Tags:

2

Answers


  1. 0

    Try below – may be you don’t add speciesname in group by clause

    SELECT `speciesname`, sum(`total_species_catch`) as species_catch 
FROM `species_estimation` GROUP by `speciesname` 
ORDER by `species_catch` DESC LIMIT 0,10
    Login or Signup to reply.
  2. 0

    The quick fix here is to just include speciesname in the GROUP BY clause:

    SELECT
    speciesname,
    SUM(total_species_catch) AS species_catch
FROM species_estimation
GROUP BY
    speciesname,
    speciesid
ORDER BY
    species_catch DESC
LIMIT 0,10;

    It probably also makes sense to include speciesid in the SELECT clause as well, just in case two names happen to be the same, and the id could be used to distinguish the duplicates.

    Note that your current query actually is ANSI compliant, assuming that the speciesid uniquely determines the speciesname. If you wanted to use your current version, you could disable ONLY_FULL_GROUP_BY mode, and the query would then run, as you expect. But, you might want to keep this mode turned on, in general.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search