Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.4
-
None
Description
Test table / data:
create table win_test (name varchar(10), cnt int);
|
insert into win_test values ('Fred', 23), ('Fred', 35), ('Joe', 10);
|
Query that fails, note values for pct_of_total are null:
select q.name, q.row_cnt, round(100 * (q.row_cnt / sum(q.row_cnt) over (order by q.name rows between unbounded preceding and unbounded following)),2) pct_of_total from (select name, count(*) row_cnt, sum(cnt) sum_cnt from win_test group by 1) q;
|
+------+---------+--------------+
|
| name | row_cnt | pct_of_total |
|
+------+---------+--------------+
|
| Fred | 2 | NULL |
|
| Joe | 1 | NULL |
|
+------+---------+--------------+
|
removing the 100 * from the pct_of_total expression results in correct values, however as a fraction rather than the desired percentage values:
select q.name, q.row_cnt, round((q.row_cnt / sum(q.row_cnt) over (order by q.name rows between unbounded preceding and unbounded following)),2) pct_of_total from (select name, count(*) row_cnt, sum(cnt) sum_cnt from win_test group by 1) q;
|
+------+---------+--------------+
|
| name | row_cnt | pct_of_total |
|
+------+---------+--------------+
|
| Fred | 2 | 0.67 |
|
| Joe | 1 | 0.33 |
|
+------+---------+--------------+
|
The original query works on columnStore 1.0.7 (which has our own window function implementation on top of 10.1) correctly and is a pattern for calculating percentage of totals.
Another workaround is to remove the brackets after the 100 *:
select q.name, q.row_cnt, round(100 * q.row_cnt / sum(q.row_cnt) over (order by q.name rows between unbounded preceding and unbounded following),2) pct_of_total from (select name, count(*) row_cnt, sum(cnt) sum_cnt from win_test group by 1) q;+------+---------+--------------+| name | row_cnt | pct_of_total |+------+---------+--------------+| Fred | 2 | 66.67 || Joe | 1 | 33.33 |+------+---------+--------------+