0
SELECT unitidentifier, unitdiscordid, count(unitdiscordid) 
FROM warnings 
GROUP BY unitdiscordid 
ORDER BY count(unitdiscordid) DESC 
LIMIT 1

This is the query I'm running and I get the following error:

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'libertyr_cad.warnings.unitidentifier' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I tried altering the query but still no luck, I know there is a fix by editing the sql_mode but I would prefer if possible to find a fix without doing that.

cursorrux
  • 1,382
  • 4
  • 9
  • 20
Hamz
  • 44
  • 6
  • Run this command: desc warnings and post it here – Yuri Melo Apr 21 '21 at 16:14
  • https://gyazo.com/3c84acef9a7b0f9b401c59eaaf1d2a0c @YuriMelo – Hamz Apr 21 '21 at 16:33
  • 1
    The improper solution is here: https://stackoverflow.com/questions/23921117/disable-only-full-group-by The proper solution is to group by `unitidentifier, unitdiscordid` . – Luuk Apr 21 '21 at 16:39
  • See my answer to https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e/13999903#13999903 – Bill Karwin Apr 21 '21 at 21:45

1 Answers1

0

When you are doing a GROUP BY, you are doing an aggregation and your individual, record-level fields are not necessarily accessible. You will need to add the fields you use into the GROUP BY in order to ensure that they are taken into account for the aggregation:

SELECT unitidentifier, unitdiscordid, count(unitdiscordid) 
FROM warnings 
GROUP BY unitidentifier, unitdiscordid 
ORDER BY count(unitdiscordid) DESC 
LIMIT 1
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175