编辑以下查询在给定某些条件的情况下为我提供了2019年所有险种的平均值,(请注意,三元组:数字,名称和险种在首次选择中构成要素,并且排除了0以下的总和;我需要获取按覆盖率分组的每年这些过滤后元素的平均价格)
Select ROUND(AVG(Amount), 2) average_2019, coverage, count(*) from
(SELECT number, name, SUM(price) Amount, coverage, count(*) FROM myTable
where (To_date("Activity Date", 'mm-dd-yyyy') between TO_DATE('01/01/2019','mm/dd/yyyy') and TO_DATE('01/01/2019','mm/dd/yyyy'))
group by number, name, coverage having SUM(price)>0)
group by coverage;
我还想显示其他年份的平均值,例如2018年和2019年,但我还没有找到一种方法来做到这一点。
输出给出3列:
但是我想要5列,包括2018年:
我尝试使用“ case when”来过滤年份,但随后我将无法按三元组(名称,编号,覆盖率)进行选择,而我需要这些平均值。有没有办法做到这一点?
我创建了一个这样的表:
create table myTable (numero number
, name varchar2(50)
, price number
, coverage number
, activity_date date);
并插入一些数据:
insert into myTable values (1, 'name1', 20, 50, to_date('02.10.2018','dd.mm.yyyy'));
insert into myTable values (2, 'name2', 10, 50, to_date('05.10.2018','dd.mm.yyyy'));
insert into myTable values (3, 'name3', 40, 50, to_date('02.10.2019','dd.mm.yyyy'));
insert into myTable values (4, 'name3', 60, 50, to_date('10.10.2019','dd.mm.yyyy'));
这是子查询:
SELECT numero
, name
, SUM(price) Amount
, coverage
, count(*)
, max(extract(year from activity_date)) year
FROM myTable
where (activity_date between TO_DATE('02.10.2018','dd.mm.yyyy')
and TO_DATE('20.10.2019','dd.mm.yyyy'))
group by numero
, name
, coverage having SUM(price) > 0;
和带有子查询的查询:
SELECT coverage as coverages,
round(avg(case when year = 2018 then Amount end), 2) average2018,
count( case when year = 2018 then Amount end) count_2018,
round(avg(case when year = 2019 then Amount end), 2) average2019,
count( case when year = 2019 then Amount end) count_2019
FROM
(SELECT numero
, name
, SUM(price) Amount
, coverage
, count(*)
, max(extract(year from activity_date)) year
FROM myTable
where (activity_date between TO_DATE('02.10.2018','dd.mm.yyyy')
and TO_DATE('20.10.2019','dd.mm.yyyy'))
group by numero
, name
, coverage having SUM(price) > 0)
group by coverage;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句