Error with extra attribute when grouping items by categories

Question

Error with extra attribute when grouping items by categories

Navigation

#1 by (0 votes)

2

I have the following tables:

categories

   |------|--------|
   |  id  |  name  |
   |------|--------|
   |  1   |   c1   |
   |------|--------|
   |  2   |   c2   |
   |------|--------|
   |  3   |   c3   |
   |------|--------|

items

   |------|---------|--------|-------------|
   |  id  |  catid  |  name  | modified_by |
   |------|---------|--------|-------------|
   |   1  |    1    |   i1   |    juan     |
   |------|---------|--------|-------------|
   |   2  |    1    |   i2   |    pedro    |
   |------|---------|--------|-------------|
   |   3  |    2    |   i3   |    pedro    |
   |------|---------|--------|-------------|
   |   4  |    2    |   i4   |    josé     |
   |------|---------|--------|-------------|
   |   5  |    3    |   i5   |    pedro    |
   |------|---------|--------|-------------|

Y I execute the following query:

SELECT c.id, c.name, COUNT(i.id) AS countid 
FROM categories AS c 
INNER JOIN items AS i ON i.catid = c.id 
WHERE c.id IN (1,2,3) GROUP BY i.catid

What I want is to group the number of IDs that each category has when making the count of the items in that category, but I get the following error

# 1055 - Expression # 4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.i.modified_by' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode = only_full_group_by

If I remove the modified_by attribute in select the error disappears but I want to keep it. How can I solve this?

mysql sql

asked by Rene Limon 06.03.2018 в 01:22

source

1 answer

Run RestTemplate with an external resource on HTTPS I do not see the accents in a printf in php when running it from cmd Windows on the command line

score 0 · Answer 1

As you are told, the concrete error is the result of not adding all the fields of the selection in the grouping. Only the aggregate functions will not need it.

Try not to add excessive fields in the GROUP BY clause, you can create the grouping in a new view or temporary table and from it perform LEFT JOIN with the rest of the values you need.