大师求助：下面的求出了一个月某字段的总数

[lu163417]

select distinct(daima),decode(daima,'1','上海青','2','白萝卜','3','小白菜','4','西红柿','5','土豆','6','黄瓜','7','薄皮青椒','8','茄子','9','四季豆','10','芹菜','11','包菜','12','洋葱','13','冬瓜','14','花菜','15','大白菜','16','生菜','17','胡萝卜','18','藕','') as cm,
(select sum(to_number(field1)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd01'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field1,
(select sum(to_number(field2)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd01'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field2,
(select sum(to_number(field1)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd02'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field3,
(select sum(to_number(field2)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd02'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field4,
(select sum(to_number(field1)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd03'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field5,
(select sum(to_number(field2)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd03'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field6,
(select sum(to_number(field1)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd04'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field7,
(select sum(to_number(field2)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd04'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field8,
(select sum(to_number(field1)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd05'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field9,
(select sum(to_number(field2)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd05'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field10,
(select sum(to_number(field1)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd06'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field11,
(select sum(to_number(field2)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd06'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field12,
(select sum(to_number(field1)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd07'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field13,
(select sum(to_number(field2)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd07'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field14,
(select sum(to_number(field1)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd08'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field15,
(select sum(to_number(field2)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd08'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field16,
(select sum(to_number(field1)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd09'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field17,
(select sum(to_number(field2)) from dyna_dataset_nfcp_range1 r where r.reportinstance_guid in(select i.reportinstance_guid from rpt7_reportinstance i where i.template_guid='AC100142FFFFFFFF8E6F8CAF00001075' and i.verifystatus>=0 and i.so_instance_code='pjcd09'and i.reportperiod_guid in (select distinct(d.reportperiod_guid) from Rpt7_ReportPeriod d where substr(d.reportperiod_code,5,7) =?)) and r.daima=f.daima ) as field18
 from dyna_dataset_nfcp_range1 f ORDER BY to_number(daima)

field1是总价格，field2是总销量，怎么能求出这月的平均值，按照月份不同求出平均值

[大明湖畔_帅锅]

结帖率：0.00%

[lu163417]

哎  不用了自己解决了，把前面的sum改成avg就ok了

[我心飞翔]

呵呵，想想就解决了，呵呵。