1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Partition by clause is counting one extra.

Discussion in 'SQL PL/SQL' started by js98, Apr 20, 2018.

  1. js98

    js98 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    usa
    When I use count by using partition by, I get one extra number in my count. Whereas, when I count by using group by clause I get one less. Why is that?

    Thanks in advance.

    Partition by query;

    Select distinct market_location,
    count(distinct case when upper(source_method) = 'abc' and upper(source_name) ='AIM' then client_number else 0 end) over (partition by market_location)
    from table1

    result = 56



    Group by query;

    SELECT market_location, count(distinct client_number)
    from table1
    where upper(source_name) = 'AIM'
    and upper(source_method) = 'abc'
    by market_location

    result = 55
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,600
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The CASE statement is returning a 0 for unmatched records; the 'straight' count(*) does not.