Как посчитать дубли записей бд по нескольким столбцам, но чтобы при этом в других столбцах были только уникальные значения?

Question

[lurulu]

Есть несколько таблиц



В рамках них мне надо получить

Задача 1:

Посчитать количество девайсов одного поколения, с одинаковыми параметрами, но разными цветами

Объединить таблицы получается, затем ищу дубли по параметрам, а вот как посчитать дубли с разными цветами - не понимаю (distinct не работает)

Вот что написала:

SELECT d.color_id, generation_id, name, volume, capacity, type_of_connector FROM device
left join (SELECT device_id, GROUP_CONCAT(color_id separator ',') as color_id from device2color_body group by device_id) as d on d.device_id=device.id
where generation_id IN (SELECT generation_id from device group by generation_id having count(*)>1)
and name IN (SELECT name from device group by name having count(*)>1 )
and volume IN (SELECT volume from device group by volume having count(*)>1)
and capacity IN (SELECT capacity from device group by capacity having count(*)>1)
and type_of_connector IN (SELECT type_of_connector from device group by type_of_connector having count(*)>1)
order by generation_id, name;

Задача 2
Посчитать количество девайсов с одного покления, с одинаковыми наименованиями но разными параметрами volume, capacity и цвета.

Answer 1

[Rsa97]

Правильно я понимаю, что в случае 1 вы просто хотите получить количество разных цветов для устройств с одинаковым набором параметров?

SELECT `d`.`generation_id`, `d`.`Name`, `d`.`volume`, `d`.`capacity`,
       `d`.`type_of_connector`, COUNT(DISTINCT `c`.`color_id`) AS `color_count`
  FROM `device` AS `d`
  JOIN `device2color_body` AS `c` ON `c`.`device_id` = `d`.`id`
  GROUP BY `d`.`generation_id`, `d`.`Name`, `d`.`volume`, `d`.`capacity`,
           `d`.`type_of_connector`

2.

SELECT `generation_id`, `Name`, COUNT(*) AS `count`
  FROM (
    SELECT DISTINCT `d`.`generation_id`, `d`.`Name`, `d`.`volume`, `d`.`capacity`,
           `d`.`type_of_connector`, `c`.`color_id`
      FROM `device` AS `d`
      JOIN `device2color_body` AS `c` ON `c`.`device_id` = `d`.`id`
  ) AS `t`
  GROUP BY `generation_id`, `Name`

Comments

[lurulu]

В первом случае в рамках такого запроса получается не тот результат, что ожидается.
Считается для каждого девайсов количество разных цветов.
Мне же надо почитать количество девайсов, которые задублированы по параметрам, но отличаются по цветам.
То есть мне не нужны уникальные записи в данном случае, я их хочу исключить.

[lurulu]

Добавила having count(*)>1 and color_count>1 - так правильно получается