Множественная агрегатная функция в SELECT с различными условиями, в которых

#mysql #sql

Вопрос:

Учитывая эти две таблицы:

 Goals(Id,Player,isAuto)
Players(Id,Name,Team)

Я хочу знать общее количество целей и автоматических целей для каждой команды.

Мой вопрос пока что:

 SELECT p.Team,COUNT(g.Id) AS Total,COUNT(g.isAuto) AS Auto
FROM Players p
JOIN Goals g
  ON p.Id=g.Player
WHERE g.isAuto=True
GROUP BY p.Team

Проблема с этим запросом заключается в том, что условие isAuto влияет на результат первой COUNT функции, и я не могу понять, как это сделать, поэтому условие isAuto влияет только на соответствующую COUNT функцию .

1. Вы можете использовать СЧЕТЧИК или СУММУ выражения КОНЦА РЕГИСТРА, чтобы эта логика не влияла на строки. SELECT COUNT(CASE WHEN x > 5 AND g.isAuto=True THEN 1 END), ... . Обратите внимание, что COUNT и SUM игнорируют значение null, полученное при отсутствии ELSE предложения или при ELSE null END явном использовании в выражении.

2. @Джон Армстронг Спасибо!

3. Пометьте свой вопрос базой данных, которую вы используете,.

Ответ №1:

 SELECT p.Team
     , COUNT(g.Id) AS Total
     , COUNT(CASE WHEN g.isAuto = True THEN 1 END) AS Auto
  FROM Players p
  JOIN Goals g
    ON p.Id=g.Player
 GROUP BY p.Team
;

Примечание: В тех случаях, когда мы хотим показать игроков / команды, у которых нет целей, используйте внешнее соединение. В противном случае внутреннее соединение в порядке.

1. Я не совсем понимаю примечание об использовании внешнего соединения для игроков без целей: если у игрока нет целей, он не должен отображаться в таблице целей, поэтому он все равно не будет включен в подсчеты, верно?

2. Все в порядке. Мой комментарий был на всякий случай, если вам нужен отчет для всех команд/пользователей, даже если они не забили голов. Если вам не нужна эта деталь, внутреннее соединение подойдет.

Ответ №2:

Проблема с этим запросом заключается в том, что условие isAuto влияет на результат первой функции ПОДСЧЕТА, и я не могу понять, как сделать так, чтобы условие в isAuto влияло только на соответствующий СЧЕТЧИК.

Результаты агрегатных функций вычисляются по строкам из источника, указанного в FROM предложении, которые удовлетворяют любым критериям, указанным в WHERE предложении. Вы можете думать об этом как о строках, отфильтрованных по WHERE критерию, прежде чем они будут объединены в группы, по которым вычисляются агрегатные функции. Вы не можете сделать WHERE предложение применимым только к соответствующему подмножеству выбранных агрегатных функций.

Но есть и другие подходы к вычислению результата, который вы пытаетесь получить. В частности, если goals.isauto целочисленное поле ограничено только значениями 0 , и 1 тогда вы можете определить SUM(g.isauto) вместо COUNT(g.isauto) :

 SELECT p.Team,COUNT(g.id) AS Total, SUM(g.isAuto) AS Auto
FROM Players p
JOIN Goals g
  ON p.Id=g.Player
WHERE g.isAuto=True
GROUP BY p.Team

Если домен isauto является чем-то другим, то вы можете вычислить сумму CASE или другое выражение, которое выполняет соответствующее преобразование. Например, SUM(CASE WHEN g.isAuto > 0 THEN 1 ELSE 0 END) .

Обратите также внимание , что COUNT(g.id) подсчитывается количество строк в группе, которые имеют ненулевое значение g.id , но если g.id это ненулевой первичный ключ, то это всегда будут все строки, более четко выраженные как COUNT(*) .