基本药物占比

select 科室,sum(总成本) as 总成本,sum(总零售) as 总零售,sum(基成本) as 基成本,sum(基零售) as 基零售,sum(差价) as 差价,
sum(基成本)/sum(总成本) As 成本比,sum(基零售)/sum(总零售) As 零售比
from (Select c.名称 As 科室,sum(a.成本金额) As 总成本,sum(a.零售金额) As 总零售,0 as 基成本,0 as 基零售,sum(a.差价) As 差价
From (Select 药品id,对方部门id,Sum(零售金额) As 零售金额, Sum(成本金额) As 成本金额, Sum(差价) As 差价
       From 药品收发记录
       Where 审核日期 Between /*B0*/TO_DATE('2020-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS')/*E0*/ And /*B1*/TO_DATE('2023-09-30 23:59:59','YYYY-MM-DD HH24:MI:SS')/*E1*/
       And 单据 In (8,9,10) And 入出系数 = -1 Having Sum(实际数量) <> 0 Or Sum(零售金额) <> 0 Or Sum(差价) <> 0
       Group By 药品id,Id,对方部门id) a, 部门表 c, 药品规格 d
Where a.药品id=d.药品id And a.对方部门id=c.Id and a.对方部门id /*B2*/is not null/*E2*/
Group By c.名称

union all

Select  c.名称 As 科室,0 as 总成本,0 as 总零售,sum(a.成本金额) As 基成本,sum(a.零售金额) As 基零售,sum(a.差价) As 差价
From (Select 药品id,对方部门id,Sum(零售金额) As 零售金额, Sum(成本金额) As 成本金额, Sum(差价) As 差价
       From 药品收发记录
       Where 审核日期 Between /*B0*/TO_DATE('2020-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS')/*E0*/ And /*B1*/TO_DATE('2023-09-30 23:59:59','YYYY-MM-DD HH24:MI:SS')/*E1*/
       And 单据 In (8,9,10) And 入出系数 = -1 Having Sum(实际数量) <> 0 Or Sum(零售金额) <> 0 Or Sum(差价) <> 0
       Group By 药品id,Id,对方部门id) a, 部门表 c, 药品规格 d
Where a.药品id=d.药品id And d.基本药物 is not null And a.对方部门id=c.Id and a.对方部门id /*B2*/is not null/*E2*/
Group By c.名称)
having sum(总成本) <> 0 or sum(总零售) <> 0 or sum(基成本) <> 0 or sum(基零售) <> 0
group by 科室

Union All
select 科室,sum(总成本) as 总成本,sum(总零售) as 总零售,sum(基成本) as 基成本,sum(基零售) as 基零售,sum(差价) as 差价,
sum(基成本)/sum(总成本) As 成本比,sum(基零售)/sum(总零售) As 零售比 From
(select '总合计' As 科室,sum(总成本) as 总成本,sum(总零售) as 总零售,sum(基成本) as 基成本,sum(基零售) as 基零售,sum(差价) as 差价
from (Select c.名称 As 科室,sum(a.成本金额) As 总成本,sum(a.零售金额) As 总零售,0 as 基成本,0 as 基零售,sum(a.差价) As 差价
From (Select 药品id,对方部门id,Sum(零售金额) As 零售金额, Sum(成本金额) As 成本金额, Sum(差价) As 差价
       From 药品收发记录
       Where 审核日期 Between /*B0*/TO_DATE('2020-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS')/*E0*/ And /*B1*/TO_DATE('2023-09-30 23:59:59','YYYY-MM-DD HH24:MI:SS')/*E1*/
       And 单据 In (8,9,10) And 入出系数 = -1 Having Sum(实际数量) <> 0 Or Sum(零售金额) <> 0 Or Sum(差价) <> 0
       Group By 药品id,Id,对方部门id) a, 部门表 c, 药品规格 d
Where a.药品id=d.药品id And a.对方部门id=c.Id and a.对方部门id /*B2*/is not null/*E2*/
Group By c.名称

union all

Select '总合计' As 科室,0 as 总成本,0 as 总零售,sum(a.成本金额) As 基成本,sum(a.零售金额) As 基零售,sum(a.差价) As 差价
From (Select 药品id,对方部门id,Sum(零售金额) As 零售金额, Sum(成本金额) As 成本金额, Sum(差价) As 差价
       From 药品收发记录
       Where 审核日期 Between /*B0*/TO_DATE('2020-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS')/*E0*/ And /*B1*/TO_DATE('2023-09-30 23:59:59','YYYY-MM-DD HH24:MI:SS')/*E1*/
       And 单据 In (8,9,10) And 入出系数 = -1 Having Sum(实际数量) <> 0 Or Sum(零售金额) <> 0 Or Sum(差价) <> 0
       Group By 药品id,Id,对方部门id) a, 部门表 c, 药品规格 d
Where a.药品id=d.药品id And d.基本药物 is not null And a.对方部门id=c.Id and a.对方部门id /*B2*/is not null/*E2*/
Group By c.名称)
having sum(总成本) <> 0 or sum(总零售) <> 0 or sum(基成本) <> 0 or sum(基零售) <> 0
group by 科室)
Group By 科室