select t.orderdate, count(t.ticketid),sum(t.fare),sum(t.fuel_tax),sum(t.airport_tax),sum(t.paymoney)
from metticket t
where t.status = '2'
and t.orderdate is not null
group by t.orderdate
order by t.orderdate这样统计每天的金额,就用日期分组了。20100929 1 1460 0 50 1510
20101019 1 920 0 50 970
20101020 1 800 0 50 850
20101021 2 2500 0 100 2600
20101025 5 3570 0 250 3820
20101027 4 2330 0 200 2530
20101028 3 1710 0 150 1860
20101029 6 3920 0 300 4220
20101102 1 680 0 50 730
20101105 1 870 0 50 920
......我想实现:没有数据的那天,也能查询到记录。因为没有记录,所有金额都为0
比如20101022没有记录,则为:
20101022 0 0 0 0 0
不知道怎么实现,求帮助
from metticket t
where t.status = '2'
and t.orderdate is not null
group by t.orderdate
order by t.orderdate这样统计每天的金额,就用日期分组了。20100929 1 1460 0 50 1510
20101019 1 920 0 50 970
20101020 1 800 0 50 850
20101021 2 2500 0 100 2600
20101025 5 3570 0 250 3820
20101027 4 2330 0 200 2530
20101028 3 1710 0 150 1860
20101029 6 3920 0 300 4220
20101102 1 680 0 50 730
20101105 1 870 0 50 920
......我想实现:没有数据的那天,也能查询到记录。因为没有记录,所有金额都为0
比如20101022没有记录,则为:
20101022 0 0 0 0 0
不知道怎么实现,求帮助
nvl(sum(t.airport_tax),0),nvl(sum(t.paymoney),0)
from metticket t
where t.status = '2'
and t.orderdate is not null
group by t.orderdate
order by t.orderdate
date_table as (select (sysdate-level) orderdate from dual connect by level < 365),
order_table as (select t.orderdate, count(t.ticketid) cnt,
sum(t.fare) sumfare, sum(t.fuel_tax) sumfueltax,
sum(t.airport_tax) sumairtax, sum(t.paymoney) sumpay from metticket t
where t.status = '2'
and t.orderdate is not null
group by t.orderdate
order by t.orderdate)
select date_table.orderdate,
decode(cnt,null,0,cnt),
decode(sumfare,null,0,sumfare),
decode(sumfueltax,null,0,sumfueltax),
decode(sumairtax,null,0,sumairtax),
decode(sumpay,null,0,sumpay)
from date_table left join order_table
on date_table.orderdate=order_table.orderdate
order by 1;