SQL查询如何在表中按名称分组

Question

SELECT R.CALL,
       P.NAME,
       R.PID,
       TIMESTAMPDIFF( 4, CHAR(TIMESTAMP( M.ENDTIME ) - TIMESTAMP( M.START ))) AS MINUTES
FROM ROSTER R, PLAYER_ON P, MATCH M
WHERE R.CALL = P.CALL
  AND R.PID = P.PID
  AND M.START = R.START
ORDER BY R.CALL ASC, P.NAME ASC

以上是我的查询，我成功地从数据库中获取了所需的列。

但是我还有一步要走。查询的示例输出如下: Output

我的问题是:如何按名称汇总总分钟数，例如，Edward Ellis 只会在输出中出现一次，总分钟数为 573+4933。

这是我的尝试:

SELECT CALL,
       NAME,
       PID,
       SUM(MINUTES)
FROM ( 
      SELECT R.CALL,
             P.NAME,
             R.PID,
             TIMESTAMPDIFF( 4, CHAR(TIMESTAMP( M.ENDTIME ) - TIMESTAMP( M.START ))) AS MINUTES
      FROM ROSTER R, PLAYER_ON P, MATCH M
      WHERE R.CALL = P.CALL
        AND R.PID = P.PID
        AND M.START = R.START )
GROUP BY NAME

但是有一个错误说:

SQL0119N An expression starting with "PID" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803

有人可以帮忙吗？

Answer 1

您应该学习使用正确的 join 语法(尽管这与您的错误无关)。简单规则:永远不要在from 子句中使用逗号。 始终使用明确的join 语法。

您的问题是由于 select 中的无关列不在 group by 中。此外，您不需要子查询:

  SELECT P.NAME, SUM(TIMESTAMPDIFF(4,CHAR(TIMESTAMP(M.ENDTIME)-TIMESTAMP(M.START)))) AS MINUTES
  FROM ROSTER R JOIN
       PLAYER_ON P
       ON R.CALL = P.CALL AND R.PID = P.PID JOIN
       MATCH M
       ON M.START = R.START
  GROUP BY NAME