2

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

Community
  • 1
  • 1
Gallop
  • 1,365
  • 3
  • 16
  • 28
  • What is the reason behind formatting the date query like that? – Gregology Oct 26 '12 at 12:49
  • 1
    Can you show some example input data and required output. Include more than one date. – Laurence Oct 26 '12 at 12:59
  • Please also refer to the link provided in the question for an example – Gallop Oct 26 '12 at 13:00
  • Please show some example input and desired output. – N West Oct 26 '12 at 15:20
  • Input column is sum(rev) column...desired output is available in Divide column. Kindly help with only the 2 problems I have mentioned.When I comment the day(date) condition I get wrong values,also I require grouping based on date – Gallop Oct 26 '12 at 17:50
  • 1
    2. You are not grouping on `date` you are grouping on `date,id,rev` so you will have one row for each unique combination of `date,id,rev`. 1. You have to provide some sample data, actual output and expected output. Just saying that the result is wrong is not enough for anyone to understand what issues you have. – Mikael Eriksson Oct 29 '12 at 07:33
  • I have added the output to the question please advice – Gallop Oct 29 '12 at 13:04

1 Answers1

2

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.

Andriy M
  • 76,112
  • 17
  • 94
  • 154