Over By Issue - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Over By Issue

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2011-01-02 : 22:43:20

I has SQL below:
select Comm_Consultant,Transaction_Date, Detail_Paid_Amount,Comm_Consultant,SUM(Detail_Paid_Amount) OVER (PARTITION BY Comm_Consultant) As TodaySales
from TRANSACTIONS.cvwReceiptDetail where Transaction_Date between convert(date,'2010-01-01') and convert(date,'2010-01-02')

The result it get would be somethings like below:

Comm_Consultant Transaction_Date TotalSales
01 2010-01-01 1000.00
01 2010-01-02 1000.00

My Over By will Sum the total of 2010-01-01 and 2010-01-02 into TodaySales. How can I change my over by so that it will only Sum the detail_paid_amount of 2010-01-01 only?

Sachin.Nand

2937 Posts

Posted - 2011-01-03 : 00:49:55

I dont think it is possible with sum over partition by.Instead modify your query to this


select Comm_Consultant,Transaction_Date, Detail_Paid_Amount,Comm_Consultant,
(select SUM(Detail_Paid_Amount) from TRANSACTIONS.cvwReceiptDetail where Transaction_Date='2010-01-01') As TodaySales
from TRANSACTIONS.cvwReceiptDetail where Transaction_Date between convert(date,'2010-01-01') and convert(date,'2010-01-02')

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-04 : 12:47:17

you mean return the sum as of 2010-01-01 with each of resultset?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources