Блог

У меня есть таблицы и данные для воспроизведения ошибки:

 DROP DATABASE IF EXISTS `theway`;

CREATE DATABASE IF NOT EXISTS `theway`
    DEFAULT CHARACTER SET utf8
    DEFAULT COLLATE utf8_general_ci;

USE `theway`;

DROP TABLE IF EXISTS fau<
CREATE TABLE IF NOT EXISTS fault (
    shorthand    VARCHAR(10) NOT NULL PRIMARY KEY,
    title        VARCHAR(255) NOT NULL,
    score        TINYINT NOT NULL -- 0 to 255
);

INSERT INTO fault (shorthand, title, score)
VALUES ('PARTY', 'Party with alcohol', 60);
INSERT INTO fault (shorthand, title, score)
VALUES ('WORK', 'Skip minimum 6 hour workday', 50);
INSERT INTO fault (shorthand, title, score)
VALUES ('SMOKE', 'Smoke', 50);
INSERT INTO fault (shorthand, title, score)
VALUES ('COFFEE', 'Coffee / Caffeine', 20);
INSERT INTO fault (shorthand, title, score)
VALUES ('FOOD', 'Crap food', 15);
INSERT INTO fault (shorthand, title, score)
VALUES ('ROUTINE', 'Skip daily routines', 15);
INSERT INTO fault (shorthand, title, score)
VALUES ('WORKOUT', 'Skip workout', 15);
INSERT INTO fault (shorthand, title, score)
VALUES ('SLEEP', 'Oversleep to exhaustion', 10);

DROP TABLE IF EXISTS day;
CREATE TABLE IF NOT EXISTS day (
    date         DATE NOT NULL PRIMARY KEY,
    hours_worked TINYINT NOT NULL,
    note         VARCHAR(1028) NOT NULL
);

DROP TABLE IF EXISTS day_has_fau<
CREATE TABLE IF NOT EXISTS day_has_fault (
    day_date         DATE NOT NULL,
    fault_shorthand  VARCHAR(10) NOT NULL,
    PRIMARY KEY (day_date, fault_shorthand)
);

ALTER TABLE day_has_fault ADD CONSTRAINT fk__day_has_fault__day__day_date FOREIGN KEY (day_date) REFERENCES day (date);
ALTER TABLE day_has_fault ADD CONSTRAINT fk__day_has_fault__fault__fault_shorthand FOREIGN KEY (fault_shorthand) REFERENCES fault (shorthand);

INSERT INTO day (date, hours_worked, note)
VALUES ('2019-04-20', 4, 'Just some regular day.');
INSERT INTO day_has_fault (day_date, fault_shorthand)
VALUES ('2019-04-20', 'SLEEP');
INSERT INTO day_has_fault (day_date, fault_shorthand)
VALUES ('2019-04-20', 'WORK');
INSERT INTO day_has_fault (day_date, fault_shorthand)
VALUES ('2019-04-20', 'ROUTINE');

INSERT INTO day (date, hours_worked, note)
VALUES ('2019-04-21', 6, 'Atleast did 6 hours minimum.');
INSERT INTO day_has_fault (day_date, fault_shorthand)
VALUES ('2019-04-21', 'SLEEP');
INSERT INTO day_has_fault (day_date, fault_shorthand)
VALUES ('2019-04-21', 'WORKOUT');
INSERT INTO day_has_fault (day_date, fault_shorthand)
VALUES ('2019-04-21', 'FOOD');

INSERT INTO day (date, hours_worked, note)
VALUES ('2019-04-22', 2, 'Only two hours here.');
INSERT INTO day_has_fault (day_date, fault_shorthand)
VALUES ('2019-04-22', 'WORK');

INSERT INTO day (date, hours_worked, note)
VALUES ('2019-04-23', 12, '12 hours work awesome.');
  

И первый запрос без ошибок:

 SELECT
    day.date,
    day.hours_worked,
    day.note,
    day_has_fault.fault_shorthand,
    fault.score
FROM day
LEFT JOIN day_has_fault
    ON day_has_fault.day_date = day.date
LEFT JOIN fault
    ON fault.shorthand = day_has_fault.fault_shorthand;
  

Когда я добавляю сумму и группирую по, я получаю ошибку:

 SELECT
    day.date,
    day.hours_worked,
    day.note,
    day_has_fault.fault_shorthand,
    fault.score,
    sum(fault.score) as fault_sum
FROM day
LEFT JOIN day_has_fault
    ON day_has_fault.day_date = day.date
LEFT JOIN fault
    ON fault.shorthand = day_has_fault.fault_shorthand
GROUP BY day.date;
  

Это весь ответ об ошибке:

 Error Code: 1055. Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'theway.day_has_fault.fault_shorthand' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
  

Я пытался использовать ANY_VALUE() в день.hours_worked и day.note, но это мало что дало.

Результаты вашего первоначального запроса могут быть сгруппированы по:

 day.date, day.hours_worked, day.note
  

но не по

 day_has_fault.fault_shorthand, fault.score
  

поскольку вы суммируете fault.score , сделайте это:

 SELECT
    day.date,
    day.hours_worked,
    day.note,
    sum(fault.score) as fault_sum
FROM day
LEFT JOIN day_has_fault
    ON day_has_fault.day_date = day.date
LEFT JOIN fault
    ON fault.shorthand = day_has_fault.fault_shorthand
GROUP BY day.date,
    day.hours_worked,
    day.note;
  

Смотрите демонстрацию.
И результаты:

 | date       | hours_worked | note                         | fault_sum |
| ---------- | ------------ | ---------------------------- | --------- |
| 2019-04-20 | 4            | Just some regular day.       | 75        |
| 2019-04-21 | 6            | Atleast did 6 hours minimum. | 40        |
| 2019-04-22 | 2            | Only two hours here.         | 50        |
| 2019-04-23 | 12           | 12 hours work awesome.       |           |
  

Вы можете использовать any_value() так:

 SELECT
    day.date,
    day.hours_worked,
    day.note,
    any_value(day_has_fault.fault_shorthand) fault_shorthand,
    any_value(fault.score) score,
    sum(fault.score) as fault_sum
FROM day
LEFT JOIN day_has_fault
    ON day_has_fault.day_date = day.date
LEFT JOIN fault
    ON fault.shorthand = day_has_fault.fault_shorthand
GROUP BY day.date;
  

и получаю непредсказуемые результаты для столбцов fault_shorthand и score , например:

 | date       | hours_worked | note                         | fault_shorthand | score | fault_sum |
| ---------- | ------------ | ---------------------------- | --------------- | ----- | --------- |
| 2019-04-20 | 4            | Just some regular day.       | ROUTINE         | 15    | 75        |
| 2019-04-21 | 6            | Atleast did 6 hours minimum. | FOOD            | 15    | 40        |
| 2019-04-22 | 2            | Only two hours here.         | WORK            | 50    | 50        |
| 2019-04-23 | 12           | 12 hours work awesome.       |                 |       |           |
  

Редактировать
Вы можете получить сгруппированные значения через запятую fault.shorthand с помощью group_concat() :

 SELECT
    day.date,
    day.hours_worked,
    day.note,
    sum(fault.score) as fault_sum,
    group_concat(fault.shorthand) shorthand
FROM day
LEFT JOIN day_has_fault
    ON day_has_fault.day_date = day.date
LEFT JOIN fault
    ON fault.shorthand = day_has_fault.fault_shorthand
GROUP BY day.date,
    day.hours_worked,
    day.note;
  

Смотрите демонстрацию.