sql - SQL：SELECT date BETWEEN 错误（postgres）

Question

我有下表

CREATE TABLE history (
    id VARCHAR,
    a_date DATE,
    logins INTEGER
);

我不确定为什么在使用以下内容时会出现错误：

SELECT a_date 
      , SUM(logins) AS total_logins
      , VARIANCE(logins) AS varince_over_time
FROM history
WHERE
a_date BETWEEN '2018-05-10' AND '2018-05-30'

即使当我运行时它工作得很好：

SELECT a_date 
FROM history
WHERE 
a_date 
BETWEEN '2018-05-10' AND '2018-05-30'

或者

SELECT a_date 
      , SUM(logins) AS total_logins
      , VARIANCE(logins) AS varince_over_time
FROM history
GROUP BY a_date 

所以不确定实际问题是什么

SELECT a_date 
      , SUM(logins) AS total_logins
      , VARIANCE(logins) AS varince_over_time
FROM history
WHERE
a_date BETWEEN '2018-05-10' AND '2018-05-30'

谢谢，有什么帮助！

标签： sqlpostgresql

Answer 1

如果你想使用聚合函数，你仍然需要 GROUP BY SUM。

SELECT a_date, 
       SUM(logins) AS total_logins, 
       VARIANCE(logins) AS varince_over_time
FROM history
WHERE a_date BETWEEN '2018-05-10' AND '2018-05-30'
GROUP BY a_date;