请问oracle中group by 能用在两张表的关联上吗？请高手指点

Question

select avg(a.average_value) average_value,a.kpi_id kpi_id,
                    a.unit_id unit_id,
                    substr(to_char(a.analyze_time,'yyyy-mm-dd hh24-mi-ss'),0,10) analyze_time,
                    b.kpi_name kpi_name,'广西营帐数据库１' device_name, '广西营帐数据库１' usage  
                    from tb_rpt_analyze200612 a,tb_cde_kpi b 
                    where a.unit_id like '%bill_db_1%' 
                    and a.unit_id like '10-10-20%' 
                    and a.kpi_id = b.kpi_id 
                    group by a.kpi_id,a.unit_id,
                    substr(to_char(a.analyze_time,'yyyy-mm-dd hh24-mi-ss'),0,10)
这个　sql中在oracle中执行有错，我看不出来错在哪里，不知是不是group by 不能用在两张表的关联上？？？？？？请高手指点，谢谢

Answer 1

支持的,试下
select avg(a.average_value) average_value,
       a.kpi_id kpi_id,
       a.unit_id unit_id,
       substr(to_char(a.analyze_time,'yyyy-mm-dd hh24-mi-ss'),0,10) analyze_time,
       b.kpi_name kpi_name,
       '广西营帐数据库１' ' device_name, 
       '广西营帐数据库１' ' usage  
                    from tb_rpt_analyze200612 a,tb_cde_kpi b 
                    where a.unit_id like '%bill_db_1%' 
                    and a.unit_id like '10-10-20%' 
                    and a.kpi_id = b.kpi_id 
                    group bya.kpi_id kpi_id,
                    a.unit_id unit_id,
                    substr(to_char(a.analyze_time,'yyyy-mm-dd hh24-mi-ss'),0,10) analyze_time,
                     b.kpi_name kpi_name,
                     '广西营帐数据库１' device_name, 
                     '广西营帐数据库１' usage  ---另外,usage  是oracle的关键字吧

Answer 2

b.kpi_name kpi_name,'广西营帐数据库１' device_name, '广西营帐数据库１' usage  
没有在group by中，不能select，楼主看看group by的用法吧。这样就可以：
select avg(a.average_value) average_value,a.kpi_id kpi_id,
                    a.unit_id unit_id,
                    substr(to_char(a.analyze_time,'yyyy-mm-dd hh24-mi-ss'),0,10) analyze_time,
                    b.kpi_name kpi_name,'广西营帐数据库１' device_name, '广西营帐数据库１' usage  
                    from tb_rpt_analyze200612 a,tb_cde_kpi b 
                    where a.unit_id like '%bill_db_1%' 
                    and a.unit_id like '10-10-20%' 
                    and a.kpi_id = b.kpi_id 
                    group by a.kpi_id,a.unit_id,
                    substr(to_char(a.analyze_time,'yyyy-mm-dd hh24-mi-ss'),0,10)
                    b.kpi_name kpi_name,'广西营帐数据库１', '广西营帐数据库１'