Sum OVER Partition By, Not giving expected result

Question

I have set of data, which shows minutes used by a "child contract" on a daily basis. I need to have a running total of the minutes consumed by the child contract so that we can keep track.

This is the code I used.

Select 
    "combined"."id", 
    "combined"."ContractCustomer", 
    "combined"."ContractCustomerChild", 
    "combined"."Minutes",
     SUM( "combined"."Minutes") OVER (PARTITION BY      "combined"."ContractCustomerChild" ORDER BY "combined"."id") As "CustomerMinutes",
FROM "dbo"."combined"

I expect the sum to be restart for every contract child, however, it does seem to behave that way.

This is the result i got:

   ID CustomerContractChild Minutes CustomerMinutes 
       1 20150101+C1          1000       1000     
       2 20150101+C1          2000       3000 
       3 20150101+c2          2500       5500

This is what I expect

   ID CustomerContractChild Minutes CustomerMinutes 
       1 20150101+C1          1000       1000     
       2 20150101+C1          2000       3000 
       3 20150101+c2          2500       2500

What did I do wrongly?

Your expectation looks correct. Can you set up a SQL Fiddle? And, why are you using double quotes? — Gordon Linoff, Nov 03 '15 at 03:08
The code were written in a java client connecting to the Server va JDBC, that's the reason I had the double quote. — AperurePro, Nov 03 '15 at 03:40
Oh, I had removed a final line from this snippet. The data is being returned i.e the were not syntax error. The only issue now is the result sets. I am wondering if its the JDBC giving this problem. — AperurePro, Nov 03 '15 at 03:51
What does the data look like in the source table? Can you share that please? — shree.pat18, Nov 03 '15 at 03:56
The data is smilar. It just does not have the cumulative column. — AperurePro, Nov 03 '15 at 04:06
Your columns in select and output don't match. Please provide actual statement. At the moment I see no error, it should work as you expecting. — Giorgi Nakeuri, Nov 03 '15 at 04:31
What is ContractCustomer? You mentioned that in the SELECT but not in result. — Mangal Pardeshi, Nov 03 '15 at 04:40
Also there must be syntax error in ur query. There exists , before FROM — Esty, Nov 03 '15 at 05:40

score 0 · Answer 1 · answered Nov 03 '15 at 05:45

0

I have run this and get exact output u required. This is same as yours without ContractCustomer column (u don't show it in result) and column ContractCustomerChild in code is showing CustomerContractChild in results and removing , before FROM.

Select 
    "combined".ID, 
    "combined"."CustomerContractChild", 
    "combined"."Minutes",
     SUM( "combined"."Minutes") OVER (PARTITION BY      "combined"."CustomerContractChild" ORDER BY "combined"."id") As "CustomerMinutes"
FROM "dbo"."combined"

answered Nov 03 '15 at 05:45

Esty

1,882
3
17
36

Thank you for the taking the time. Yes my mistake I do have a column with ContractCustomer. In essence the contractcustomer is the same customercontractchild except for the suffix +C1, +C2,+C3. – AperurePro Nov 03 '15 at 06:23
Doesn't matter. And then your query was correct for required output. Did ur problem solved?? – Esty Nov 03 '15 at 07:25
For complex running total problem this post may be useful. [link](http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance) – Esty Nov 03 '15 at 07:29
I rerain the code with contractcustomer and without, the results in the same. It seems the result became wrong after I joint this view to another table. I have to trace it. – AperurePro Nov 03 '15 at 07:39
Tanjim Rahman, thanks for the link. I'm trying to trace at which point the results get messed up. I have removed my joints and will track the result through every step. – AperurePro Nov 03 '15 at 09:30

score 0 · Answer 2 · answered Nov 03 '15 at 12:12

0

Thank you for the responses. My eyes must have been playing trick on me due to sorting etc. I reviewed the results and they look correct. I will close this for now. I'll sample a few more results and have a go if they turn out wrong.

answered Nov 03 '15 at 12:12

AperurePro

11
2

Sum OVER Partition By, Not giving expected result

2 Answers2