Trying to use OVER(Partition...)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trying to use OVER(Partition...)

Author

Topic

b11091019
Starting Member

15 Posts

Posted - 2012-01-24 : 15:12:27

Hi -- I'm trying to use an aggregate function with OVER() but I'm not getting the results I expect. Here's what I'm working with:

select cust_id_num, bill_acct_num, SRVC_ID, rev_amt_prev_dec
from or_iw.dbo.iw_revenue where SRVC_ID = 'FOH00006'

gives me:

cust_id_num bill_acct_num SRVC_ID rev_amt_prev_dec
------------ ------------- ------------------------ ---------------------------------------
000000233777 010000252745 FOH00006 137.38
000000233777 010000252745 FOH00006 0.00

Using this query:

select cust_id_num, bill_acct_num, SRVC_ID,
sum(rev_amt_prev_dec) over(partition by cust_id_num,bill_acct_num, srvc_id) total_dec
from or_iw.dbo.iw_revenue
where SRVC_ID = 'FOH00006'

I get:

cust_id_num bill_acct_num SRVC_ID total_dec
------------ ------------- ------------------------ ---------------------------------------
000000233777 010000252745 FOH00006 137.38
000000233777 010000252745 FOH00006 137.38

However, I expected just one line output for this query giving the total. Why am I getting two lines and what is the right way to get just one line?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-24 : 15:53:32

The OVER() clause is a windowing function (allows you to do computations within a specified window without doing any grouping). So you would see the two rows if you used the windowing function.

This article and the second and third part of it are really good: http://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server/

If you want only one row, GROUP BY would get you that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources