February 18, 2011 at 10:05 am
Does anyone else has an experience of using Over(Partition By) and getting an incomplete data set. I am doing a sum(amount) over (partition by) account and getting a smaller dollar amount than it should be. This is proven by using the detail pull for this same query - ie, select account, amount, etc., and getting more data. I am also using SELECT DISTINCT for the summarized data. Would this make a difference? I am sure there is some simple reason for the disconnect. I am just not seeing it.
Here is the query that is giving the less than accurate data. Even if I execute without the GROUP BY clause, a number of records are not being included.
SELECT DISTINCT
Account
, sum(amount) over (partition by account) Total
, count (account) over (partition by account) Count
FROM tbl_Amount
WHERE number LIKE '79%'
AND date = dbo.dbFunc_Determine_CurRptMth
GROUP BY Account
, Amount
February 18, 2011 at 2:00 pm
My guess is that the DISTINCT and the aggregate functions aren't playing nice.
Have you tried it without the DISTINCT and the GROUP BY?
SELECT
Account
, sum(amount) over (partition by account) Total
, count (account) over (partition by account) Count
FROM tbl_Amount
WHERE number LIKE '79%'
AND date = dbo.dbFunc_Determine_CurRptMth
_____________________________________________________________________
- Nate
February 18, 2011 at 2:10 pm
I would agree that the distinct is most likely the cause of less than accurate results.
If any rows of your dataset match, you will only get the one row.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply