1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Difference between Group by and Partition by

Discussion in 'Oracle' started by bashamsc, Mar 12, 2013.

  1. bashamsc

    bashamsc New Member

    Joined:
    May 22, 2007
    Messages:
    51
    Likes Received:
    7
    Trophy Points:
    0
    Location:
    chennai
    In below articles I have used both group by and also partition by

    So I thought to explain the difference between Group by and Partition by.

    Now we will list out below difference between two

    Group by

    1. Reduces the no. of records
    2. In select we need to use only columns which are used in group by. but we can use aggregate functions.
    3. In filter condition we need to use having clause instead of where clause.
    Partition By

    1. No. of records will not be reduced. Instead of that it will add one extra column.
    2. In select we can use N no. of columns. No restrictions.
    3. We can use where clause in filter condition apart from partition column.
    Now we will try to learn the difference by looking into below the examples.

    Take below table data which is used in the one of the article.

    Code:
    select * from callerlog 
    Output will be

    Code:
    CALLERID    CALLER    CALLEDTO    CALLER_DT    OUTGOING_SEC
    
    3        456    123        02/21/2013    10
    4        123    678        02/21/2013    20
    1        123    456        02/21/2013    30
    2        123    456        02/21/2013    10
    5        678    456        02/21/2013    40
    6        456    678        02/20/2013    20
    7        789    555        02/21/2013    10
    8        567    321        02/21/2013    100
    We have used below in one of the article

    Query by using group by

    Code:
    select caller ,count(*) as outgoing from callerlog group by caller
    Output will be as below

    Code:
    CALLER    OUTGOING
    
    123             3
    456             2
    567             1
    678             1
    789             1
    Group by has reduced the records from 8 to 5.


    Query by using partition by

    Code:
    select caller ,count(*) over (partition by caller ) as outgoing from callerlog 
    Output will be as below

    Code:
    CALLER    OUTGOING
    
    123            3
    123            3
    123            3
    456            2
    456            2
    567            1
    678            1
    789            1
    Partition by has not reduced the no. of records. Remains same.

    Below query we will not workout for group by

    Code:
    select * from callerlog group by caller
    Oracle will throw error. As we disscussed above in the differences that we can't select other than columns which are not present in group by.

    But we can use aggregate functions in select statement as in above example.


    Below query will not work for partition by


    Code:
    select caller ,count(*) over (partition by caller ) as outgoing from callerlog where outgoing >1
    Oracle will throw error. As we disscussed above in the differences that we can't use the partition by column in filter condition.

    We can use where clause as below apart from partition column.

    Code:
    select caller ,count(*) over (partition by caller ) as outgoing from callerlog where caller=123
    If we want to use the column in the filter condition we need to re-write the query as below

    Code:
    select * from 
    (
    select caller ,count(*) over (partition by caller ) as outgoing from callerlog
    )where outgoing >1
    Above is one of the disadvantage with the partition by.


    If we want to reduce the group or remove duplicate then we use

    Code:
    select caller from callerlog group by caller having count(*)=1
    or

    Code:
    select caller from callerlog group by caller having count(*)>1
    or

    Code:
    select caller from callerlog group by caller having caller=123
     
    askr and shabbir like this.

Share This Page