我试图计算一列的总和,该总和计算另一个数字列的值。我要计算总和的特定列是'COUNT(d.totalcostofrent)。
我就是根本无法将总和显示在桌子底下!
这是我的代码:
COLUMN dummy NOPRINT
COMPUTE SUM OF totalcostofrent ON DUMMY
BREAK ON dummy
COLUMN branchname HEADING 'Branch Name'
COLUMN "Revenue" HEADING 'Rental Revenue' FORMAT $99,999
select null dummy,
a.branchname,
count(c.rentalid) "Number of Rentals",
SUM(d.totalcostofrent) "Revenue"
from branch a
inner join branchstaffrole b on a.branchid = b.branchid
inner join rental c on b.staffid = c.branchstaffid
inner join rentaltool d on c.rentalid = d.rentalid
where to_char(d.DATEOUT, 'YYYY') = '&Year'
group by a.branchname
order by 4 DESC;
Branch Name Number of Rentals Rental Revenue
-------------------- ----------------- --------------
Lichfield 2 $120
Stoke-on-Trent 3 $140
Birmingham 3 $155
Stafford 3 $225
我希望它看起来像这样:
Branch Name Number of Rentals Rental Revenue
-------------------- ----------------- --------------
Lichfield 2 $120
Stoke-on-Trent 3 $140
Birmingham 3 $155
Stafford 3 $225
____
Sum $640
任何建议将不胜感激!
看来您正在尝试使用SQL * Plus命令设置报告格式。您真的很亲密,只有该列的别名:SUM(d.totalcostofrent)“ Revenue”。因此,您还需要在Compute SQL * Plus命令中使用该别名:
COMPUTE SUM OF "Revenue" ON DUMMY
这将返回:
Branch Name Number of Rentals Rental Revenue
-------------- ----------------- --------------
Lichfield 2 $120
Birmingham 3 $155
Stoke-on-Trent 3 $140
Stafford 3 $225
--------------
$640
您还可以执行以下操作(排除虚拟列):
COMPUTE SUM OF "Revenue" ON REPORT
BREAK ON REPORT
COLUMN branchname HEADING 'Branch Name'
COLUMN "Revenue" HEADING 'Rental Revenue' FORMAT $99,999
哪个返回:
Branch Name Number of Rentals Rental Revenue
-------------- ----------------- --------------
Lichfield 2 $120
Birmingham 3 $155
Stoke-on-Trent 3 $140
Stafford 3 $225
--------------
sum $640
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句