Grouping and Dividing Netezza

Question

I have the following query that divides each row in the sum(rev) column by the sum of the column.

For the following example the sum of sum(rev) column is 23193.The Divide column is derived by : row in sum(rev)/sum(rev)

select date,id,sum(rev), 
NULLIF(rev,0) / sum(rev) over() as Divide
from test 
where month(date) = 11
and year(date) = 2012
and day(date) = 02
and id = 'Client1'
group by date,id,rev
having sum(rev) <> 0
order by date


date                  id      sum(rev)      Divide

2012-11-02 00:00:00 Client1     1562.00     0.067348
2012-11-02 00:00:00 Client1     1.00        0.000043
2012-11-02 00:00:00 Client1     4689.00     0.202173
2012-11-02 00:00:00 Client1     267.00      0.011512
2012-11-02 00:00:00 Client1     16674.00    0.718924

There are 2 problems

1.) When the day(date) condition is commented the values retrieved are wrong.It does not give the correct value in the Divide Computation

    date               sum(rev)         Divide
    2012-11-02 00:00:00 1.00            0.000002
    2012-11-02 00:00:00 267.00          0.000412
    2012-11-02 00:00:00 1562.00         0.002412
    2012-11-02 00:00:00 4689.00         0.007241
    2012-11-02 00:00:00 16674.00        0.025749

2.) I want to group by date.So since we have records only for 2-11-2012 there must be only one line of record for each day

Please help fix these two errors

Ref: Find column Value by dividing with sum of a column

Answer 1

If you want to group by date and divide the daily totals by the grand total, you could do it like this:

SELECT
  date,
  SUM(rev) AS total,
  SUM(rev) / SUM(SUM(rev)) OVER () AS portion
FROM test
GROUP BY
  date
;

That is, the argument of SUM() OVER () should be a valid expression, and rev isn't a valid expression in this GROUP BY query, because rev isn't included in GROUP BY. But you can (and should) use SUM(rev) as an argument, and it will work as expected.

If you want separate results for separate clients, add id to the GROUP BY clause and add PARTITION BY id to the OVER clause of the window SUM(), like this:

SELECT
  date,
  id,
  SUM(rev) AS total,
  SUM(rev) / SUM(SUM(rev)) OVER (PARTITION BY id) AS portion
FROM test
GROUP BY
  date,
  id
;

Read more about the OVER clause in the manual.