时间戳

首页 > 解决方案 > 数学和 to_char

sql - 数学和 to_char

问题描述

对于拥有超过一名员工的分支机构，将分支机构名称、员工人数显示为 Headcount、总工资显示为 Payroll、平均工资显示为“Average Salary”。使用“$”格式格式化所有美元金额。

以下是我回答此查询的代码，但是我收到了无效号码错误。

select br_branchname, count (st_staffno) as "Headcount", 
    sum(to_char (st_salary, '$99,999.00')) as "Payroll", 
    avg(to_char (st_salary, '$99,999.00')) as "Average Salary"
from branch join staff
on st_br_branchno = br_branchno
group by br_branchname
having count (st_staffno) > 1;

标签： sqloracle

解决方案

你已经得到TO_CHAR并AVG/SUM扭转了：

select br_branchname,
       count (st_staffno) as "Headcount", 
       TO_CHAR(SUM(st_salary), '$99,999.00') as "Payroll", 
       TO_CHAR(AVG(st_salary), '$99,999.00') as "Average Salary"
from branch
INNER join staff
  on st_br_branchno = br_branchno
group by br_branchname
having count (st_staffno) > 1

您需要TO_CHAR在堆叠函数调用的“外部”进行 - 否则您将尝试计算包含 a 的字符串的平均值和总和，而 Oracle 非常正确地提出了一个关于不是有效数字'$'的拟合。$

祝你好运。

推荐阅读