时间戳

首页 > 解决方案 > GROUP BY 中不允许使用聚合函数

sql - GROUP BY 中不允许使用聚合函数

问题描述

我想根据每月的记录数生成报告。在没有的情况下，我希望返回 0 而不是什么都没有。问题是，下面的查询抛出错误聚合函数在 GROUP BY 中是不允许的。任何帮助将不胜感激。

SELECT sc.name, d.months, COUNT(s.scan_type) AS scan_count FROM 
    (SELECT CAST('2018-02-21 12:45:44+00' AS TIMESTAMP) - date_trunc('month', CAST('2018-02-21 12:45:44+00' AS TIMESTAMP) - offs) AS months 
FROM generate_series(CAST('2018-02-21 12:45:44+00' AS TIMESTAMP), CAST(now() AS TIMESTAMP), INTERVAL '1 month') AS offs ) d 
LEFT OUTER JOIN scans s ON d.months = date_trunc('month', s.datetime) 
INNER JOIN schools sc ON s.school_id = sc.id 
GROUP BY sc.name, d.months, scan_count

标签： sqlpostgresqlgroup-by

解决方案

如果我理解正确，请使用CROSS JOIN将所有日期和schools. 然后带入原始数据并聚合：

SELECT sc.name, d.months, COUNT(s.scan_type) AS scan_count 
FROM generate_series(CAST('2018-02-21 12:45:44+00' AS TIMESTAMP), 
                     CAST(now() AS TIMESTAMP),
                     INTERVAL '1 month'
                    ) d CROSS JOIN
     schools sc LEFT OUTER JOIN 
     scans s 
     ON d.months = date_trunc('month', s.datetime) AND
        s.school_id = sc.id 
GROUP BY sc.name, d.months;

推荐阅读