I'm getting a weird error when trying to convert an integer sum to a float via the CAST command.
This works:
Code: Select all
dbadmin=> SELECT d.dname, e.ename, e.sal,
dbadmin-> round(sal / sum(cast(e.sal as float)) OVER (PARTITION BY e.dname),2) sal_dept_ratio
dbadmin-> FROM emp e, dept d
dbadmin-> WHERE e.dname = d.dname;
dname | ename | sal | sal_dept_ratio
------------+--------+------+---------------
ACCOUNTING | CLARK | 2450 | 0.28
ACCOUNTING | MILLER | 1300 | 0.15
ACCOUNTING | KING | 5000 | 0.57
RESEARCH | ADAMS | 1100 | 0.1
RESEARCH | SMITH | 800 | 0.07
RESEARCH | JONES | 2975 | 0.27
RESEARCH | FORD | 3000 | 0.28
RESEARCH | SCOTT | 3000 | 0.28
SALES | ALLEN | 1600 | 0.17
SALES | JAMES | 950 | 0.1
SALES | MARTIN | 1250 | 0.13
SALES | BLAKE | 2850 | 0.3
SALES | TURNER | 1500 | 0.16
SALES | WARD | 1250 | 0.13
(14 rows)
Code: Select all
dbadmin=> SELECT d.dname, e.ename, e.sal,
dbadmin-> round(sal / cast(sum(e.sal) as float) OVER (PARTITION BY e.dname),2) sal_dept_ratio
dbadmin-> FROM emp e, dept d
dbadmin-> WHERE e.dname = d.dname;
INTERNAL: VIAssert(list->length > 0) failed
DETAIL: list.c: 39
HINT: Please report this error to Vertica; try restating your query
thanks!