select bc.outid,bc.name,sum(t.opfare) , case when sum(t.opfare) >= 50 then 100 else sum(t.opfare) * 2 end "补助金额"from rec_cust_acc t,base_customers bc where t.acccode = 101 and t.customerid = bc.customerid and t.opdt between to_date(concat('2010-08-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and to_date(concat('2010-12-06',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss') group by bc.outid,bc.name order by bc.outid 我的需求是这样的:
这个sql查出来的是员工编号、员工姓名、充值金额、补助金额列表我想在此基础上,在最后一行合计出员工总数、充值金额总数、补助金额总数。
合计sql语句我是这样写的:select count(distinct(bc.outid)),sum(sum(t.opfare)) from rec_cust_acc t,base_customers bc where t.acccode = 101 and t.customerid = bc.customerid and t.opdt between to_date(concat('2010-08-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and to_date(concat('2010-12-06',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss') group by bc.outid,bc.name,t.opfare order by bc.outid;
这个sql语句能统计出人数、充值金额总数,但不知道补助金额总数怎么算了
等待高手指点啊
这个sql查出来的是员工编号、员工姓名、充值金额、补助金额列表我想在此基础上,在最后一行合计出员工总数、充值金额总数、补助金额总数。
合计sql语句我是这样写的:select count(distinct(bc.outid)),sum(sum(t.opfare)) from rec_cust_acc t,base_customers bc where t.acccode = 101 and t.customerid = bc.customerid and t.opdt between to_date(concat('2010-08-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and to_date(concat('2010-12-06',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss') group by bc.outid,bc.name,t.opfare order by bc.outid;
这个sql语句能统计出人数、充值金额总数,但不知道补助金额总数怎么算了
等待高手指点啊
select bc.outid,bc.name,sum(t.opfare),
sum(sum(t.opfare)),
sum(case when sum(t.opfare)>=50 then 100 else sum(t.opfare) * 2 end)
from rec_cust_acc t,base_customers bc
where t.acccode = 101 and t.customerid = bc.customerid
and t.opdt between to_date(concat('2010-08-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss')
and to_date(concat('2010-12-06',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss')
group by rollup((bc.outid,bc.name,t.opfare))
order by bc.outid;--给个例子参考
with a as (
select 'a' A, 'b' B ,5 C ,2 D from dual
union all
select 'w', 'b', 1, 3 from dual
union all
select 'x' ,'x' ,3 ,1 from dual
)
select decode(grouping(A),1,'合計',A) A
,B,sum(C) C,SUM(D) D
from a
group by rollup ((a,b))A B C D
---- - ---------- ----------
a b 5 2
w b 1 3
x x 3 1
合計 9 6
你能不能在第二个sql基础上改
还有用上rollup,统计出的合计明显就不对了
sum(case when sumopfare>=50 then 100 else sumopfare*2 end)
from (select bc.outid,bc.name,
sum(t.opfare) sumopfare,
from rec_cust_acc t,base_customers bc
where t.acccode = 101 and t.customerid = bc.customerid
and t.opdt between
to_date(concat('2010-08-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss')
and to_date(concat('2010-12-06',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss')
group by bc.outid,bc.name,t.opfare)
group by rollup((outid,name))
order by outid,name