How to properly solve MySql complaint about "GROUP BY clause and contains nonaggregated column" when ONLY_FULL_GROUP_BY is ON

Question

MySQL Version 8.0 Schema SQL

CREATE TABLE IF NOT EXISTS `servers` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `companies` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `print` (
  `id` INT NOT NULL,
  `page` INT NOT NULL,
  `copy` INT NOT NULL,
  `date` DATE NOT NULL,
  `server` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_print_servers1_idx` (`server` ASC) VISIBLE,
  CONSTRAINT `fk_print_servers1`
    FOREIGN KEY (`server`)
    REFERENCES `servers` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `company_server` (
  `server` INT NOT NULL,
  `company` INT NOT NULL,
  PRIMARY KEY (`server`, `company`),
  INDEX `fk_servers_has_company_company1_idx` (`company` ASC) VISIBLE,
  INDEX `fk_servers_has_company_servers_idx` (`server` ASC) VISIBLE,
  CONSTRAINT `fk_servers_has_company_servers`
    FOREIGN KEY (`server`)
    REFERENCES `servers` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_servers_has_company_company1`
    FOREIGN KEY (`company`)
    REFERENCES `companies` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

insert into servers (id,name)
values
(1, 'server1'),
(2, 'server2'),
(3, 'server3'),
(4, 'server4');

insert into companies (id,name)
values
(1, 'company1'),
(2, 'company2'),
(3, 'company3');

insert into company_server (company,server)
values
(1,1),
(2,1),
(3,2),
(3,3);

insert into print (id,page,copy,date,server)
values
(1,2,3,'2020-1-11',1),
(2,1,6,'2020-1-12',3),
(3,4,5,'2020-1-13',4),
(4,5,3,'2020-1-15',2),
(5,3,4,'2020-1-15',4),
(6,1,2,'2020-1-16',3),
(7,2,2,'2020-1-16',4);

My query:

select 
  group_concat(c.name separator ',') as name_company,
  ss.name, 
  sum_print as sum,
  (sum_print/total) *100 as percentage
from companies c
inner join company_server cs on c.id = cs.company 
right join servers ss on ss.id = cs.server
left join (
  select
    server,
    sum(page*copy) as sum_print
  from print 
  where date between CAST('2020-1-12' AS DATE) AND CAST('2020-1-15' AS DATE)
  group by server
) tmp on tmp.server = ss.id 
cross join (
  select sum(page*copy) as total
  from print
  where date between CAST('2020-1-12' AS DATE) AND CAST('2020-1-15' AS DATE)
) tmp2
group by ss.id;

Mysql error output

Query Error: Error: ER_WRONG_FIELD_WITH_GROUP: Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tmp2.total' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

What I need:

That someone can point me, in the query above, which adjustment is missing so that the error is not thrown

DB Feedle

What I dont want:

Turn off ONLY_FULL_GROUP_BY

Extra notes:

The query works fine if ONLY_FULL_GROUP_BY is turned off
No embarrassment, I don't have good query fluency. I've already tried to read some stackoverflows answers about the same problem, but I ended up not understanding due to my limitation. I think that by being pointed out where the problem is, maybe I can better understand what the other topics were pointing out.

@Akina the trick works. But can you tell me more about it or link to where i can find a explanation about it? — Fábio, Feb 01 '22 at 20:30
@Thallius just found it. Its the select orders https://stackoverflow.com/questions/7392730/what-does-sql-clause-group-by-1-mean — Fábio, Feb 01 '22 at 20:37
*the trick works.* This is NOT a trick, this is simply an edition which makes the query logically correct. *where i can find a explanation about it?* https://dev.mysql.com/doc/refman/8.0/en/select.html *MySQL extends the use of GROUP BY to permit selecting fields that are not mentioned in the GROUP BY clause. If you are not getting the results that you expect from your query, please read the description of GROUP BY found in Section 12.20, “Aggregate Functions”.* And https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html — Akina, Feb 02 '22 at 04:53

How to properly solve MySql complaint about "GROUP BY clause and contains nonaggregated column" when ONLY_FULL_GROUP_BY is ON

0 Answers0