CODEWITHSUNDEEP
mysqlsql
kelsheikh
kelsheikh

Reputation: 1338

GROUP BY clause and contains nonaggregated column in Query

I'm having some trouble with this query displaying and the GROUP BY query. I keep on getting the same error no matter what I do with the GROUP BY. Any help is greatly appreciated.

Currently getting this error:

Error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'myData.stateName' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Here is my sql:

SELECT 
  stateAbv,
  stateName,
  '' AS cityName,
  count(*) AS state_count 
from
  myData 
GROUP BY stateAbv 
union
SELECT 
  stateAbv,
  stateName,
  city AS cityName,
  COUNT(*) AS city_count 
FROM
  myData 
GROUP BY stateAbv,
  city 
ORDER BY stateAbv,
  cityName

Upvotes: 2

Views: 22976

Answers (1)

Icculus018
Icculus018

Reputation: 1066

You are getting the error GROUP BY clause and contains nonaggregated column 'myData.stateName' because the column stateName is not in the group by clause, just as it says. So it is saying that you are grouping some rows together and when the query tries to group the stateName column, it could having different values within the rows being grouped. The query does not know which one is the right value to show in the grouped row. You need to provide a way to tell the query which value is the right value to keep in grouped row. Say you have a number column, and in the rows you are grouping together has the values 1,2,3 and 4. You can say i want the highest number from those groups of numbers and do this by MAX(column_name) and the grouped row will show 4.

In your query, if all the values in stateName are the same in the rows being grouped, just add the stateName in your group by clause.

Upvotes: 7

Related Questions