给出“不是GROUP BY表达式”错误的SQL查询

问题描述：

SELECT SUM(a.Clicks) AS Clicks, SUM(b.NoOfUsers) Users, c.WEEK_NUM, b2.ALL_TASKS FROM  
    (SELECT SUM(CLICK_CNT) AS Clicks, TO_CHAR(RQST_DT,'YYYY-MM-DD') AS DATEE FROM PER_DAY_USAGE GROUP BY RQST_DT) a,  
    (SELECT TO_CHAR(RQST_DT,'YYYY-MM-DD') AS DATEEE, WEEK_NUM FROM TIMEDIM) c,  
    (SELECT NoOfUsers, accDate FROM ((SELECT COUNT(DISTINCT RECEPIENT_ID) AS NoOfUsers, TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD') AS accDate FROM ALRT_HSTRY WHERE ACTN_TAKE_CD is not null GROUP BY TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD')) UNION ALL ( SELECT COUNT(DISTINCT RECEPIENT_ID) AS NoOfUsers, TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD') AS accDate FROM PLATFORM_ALRT WHERE ACTN_TAKE_CD is not null GROUP BY TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD')))   ) b, 
    (select sum(TOTL_ALRT_CNT) ALL_TASKS, TO_CHAR(CRTE_ON_DT,'YYYY-MM-DD') AS DATEE from FEED_HSTRY where APPL_CD like '%' group by CRTE_ON_DT)b2 
    WHERE a.datee = b.accDate AND a.datee=c.dateee AND b2.DATEE = c.dateee GROUP BY c.WEEK_NUM

它的问题b2.ALL_TASKS ALL_TASKS。如果我从SELECT中删除它的工作。给出“不是GROUP BY表达式”错误的SQL查询

明白了。此链接是有帮助的：http://stackoverflow.com/questions/1520608/ora-00979-not-a-group-by-expression – HanuAthena 2010-04-07 10:37:48

只是为了重申：您必须将所有列的SELECT放在GROUP BY中或使用函数在它们将结果压缩到单个值（如MIN，MAX或SUM）。 – HanuAthena 2010-04-07 10:59:20

答

你有添加b2.ALL_TASKS到最后group by，但你应该使用现代的JOIN语法以及试图格式化好一点：

SELECT 
    SUM(a.Clicks) AS Clicks, SUM(b.NoOfUsers) Users, c.WEEK_NUM, b2.ALL_TASKS 
    FROM (SELECT 
       SUM(CLICK_CNT) AS Clicks, RQST_DT AS DATEE 
       FROM PER_DAY_USAGE  
       GROUP BY RQST_DT 
     ) a 
     INNER JOIN (SELECT 
         RQST_DT AS DATEEE, WEEK_NUM 
         FROM TIMEDIM 
        ) c ON a.datee=c.dateee 
     INNER JOIN (SELECT 
         NoOfUsers, accDate 
         FROM (
            (SELECT 
             COUNT(DISTINCT RECEPIENT_ID) AS NoOfUsers, ACTN_TAKE_DATA_TM AS accDate 
             FROM ALRT_HSTRY 
             WHERE ACTN_TAKE_CD is not null 
             GROUP BY ACTN_TAKE_DATA_TM 
           ) 
            UNION ALL 
            (SELECT 
             COUNT(DISTINCT RECEPIENT_ID) AS NoOfUsers, ACTN_TAKE_DATA_TM AS accDate 
             FROM PLATFORM_ALRT 
             WHERE ACTN_TAKE_CD is not null 
             GROUP BY ACTN_TAKE_DATA_TM 
           ) 
          ) 
        ) b ON a.datee = b.accDate 
     INNER JOIN (SELECT 
         sum(TOTL_ALRT_CNT) ALL_TASKS, CRTE_ON_DT AS DATEE 
         from FEED_HSTRY 
         where APPL_CD like '%' 
         group by CRTE_ON_DT 
        ) b2 ON c.dateee=b2.DATEE 
    GROUP BY c.WEEK_NUM, b2.ALL_TASKS

给出“不是GROUP BY表达式”错误的SQL查询

相关推荐