Incomplete Dataset when Using Over(Partition By)

  • 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

  • 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

    @nate_hughes
  • 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