在Oracle中使用IW和MM

Question

我使用IW表示每周结果，使用MM表示每月结果。但是我总是得到一个错误：

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"

我的问题是

(每周)

SELECT 'Data'
       || ',' || TO_CHAR(d.dtime_day, 'MM/dd/yyyy')
       || ',' || NVL(o.cnt_opened, 0) --as cnt_opened
       || ',' || NVL(c.cnt_closed, 0) --as cnt_closed
FROM owner_dwh.dc_date d
  LEFT JOIN (
              SELECT
                TRUNC(t.create_time, 'IW') AS report_date,
                count(*)                   AS cnt_opened
              FROM app_account.otrs_ticket t
              WHERE t.create_time BETWEEN SYSDATE - 30 AND SYSDATE
              GROUP BY TRUNC(t.create_time)
            ) o ON d.dtime_day = o.report_date
  LEFT JOIN (
              SELECT
                TRUNC(t.close_time, 'IW') AS report_date,
                count(*)                  AS cnt_closed
              FROM app_account.otrs_ticket t
              WHERE t.close_time BETWEEN SYSDATE - 30 AND SYSDATE
              GROUP BY TRUNC(t.close_time)
            ) c ON d.dtime_day = c.report_date
WHERE d.dtime_day BETWEEN SYSDATE - 30 AND SYSDATE
ORDER BY d.dtime_day;

(每月)

SELECT 'Graph,ColumnChart,Open vs. Close Issues' FROM DUAL;

SELECT 'Data,Date,Opened,Closed' from dual;

SELECT 'Data'
       || ',' || TO_CHAR(d.dtime_day, 'MM/dd/yyyy')
       || ',' || NVL(o.cnt_opened, 0) --as cnt_opened
       || ',' || NVL(c.cnt_closed, 0) --as cnt_closed
FROM owner_dwh.dc_date d
  LEFT JOIN ( SELECT
                TRUNC(t.create_time, 'MM') AS report_date,
                count(*)                   AS cnt_opened
                FROM app_account.otrs_ticket t
               WHERE t.create_time BETWEEN SYSDATE - 365 AND SYSDATE
               GROUP BY TRUNC(t.create_time);
            ) o ON d.DTIME_DAY=o.REPORT_DATE
  LEFT JOIN ( SELECT TRUNC(t.CLOSE_TIME, 'MM') AS report_date,
                     count(*)                  AS cnt_closed
                FROM APP_ACCOUNT.OTRS_TICKET t
               WHERE t.CLOSE_TIME BETWEEN SYSDATE -365 AND SYSDATE
               GROUP BY TRUNC(t.CLOSE_TIME);
          ) c ON D.DTIME_DAY= C.REPORT_DATE
WHERE d.DTIME_DAY BETWEEN SYSDATE -365 AND SYSDATE
ORDER BY D.DTIME_DAY;

Answer 1

您必须按SELECT子句中使用的相同表达式进行分组。

因此，如果您：

SELECT TRUNC(t.CREATE_TIME, 'MM') AS report_date,count(*) AS cnt_opened

..。那你就得..。

GROUP BY TRUNC(t.CREATE_TIME,'MM')