php 使用别名指定计数并按月分组

gopyfrb3 于 2023-02-03 发布在 PHP

关注(0)|答案(1)|浏览(95)

SELECT MONTHNAME(date_added) as month,
                    (SELECT COUNT(*) FROM requests WHERE form_status = :approved) AS approved, 
                    (SELECT COUNT(*) FROM requests WHERE form_status = :denied) AS denied, 
                    (SELECT COUNT(*) FROM requests WHERE form_status = :completed) AS completed,
                    (SELECT COUNT(*) FROM requests WHERE form_status = :pending) AS pending 
                    FROM requests WHERE req_dept = :req_dept GROUP BY month

结果不是我所期望的。我想统计表单状态，并最终按月分组，但结果是每个月的计数都是一样的。我使用chartjs。

php

来源：https://stackoverflow.com/questions/75295280/specifying-count-with-alias-and-group-by-month

1条答案

按热度按时间

62lalag41#

让我们试试这个：-

WITH CTE AS (
SELECT MONTHNAME(DATE_ADDED) AS MONTH,
CASE WHEN FORM_STATUS = :APPROVED THEN 1 ELSE 0 END AS APPROVED, 
CASE WHEN FORM_STATUS = :DENIED THEN 1 ELSE 0 END AS DENIED, 
CASE WHEN FORM_STATUS = :COMPLETED THEN 1 ELSE 0 END AS COMPLETED,
CASE WHEN FORM_STATUS = :PENDING THEN 1 ELSE 0 END AS PENDING 
FROM REQUESTS WHERE REQ_DEPT = :REQ_DEPT 
) SELECT MONTH, SUM(APPROVED) AS APPROVED, SUM(DENIED) AS DENIED, SUM(COMPLETED) AS COMPLETED, SUM(PENDING) AS PENDING
GROUP BY MONTH

赞(0）回复(0）举报 2023-02-03

我来回答

php 使用别名指定计数并按月分组

1条答案

让我们试试这个：-

相关问题

热门标签

最新问答