Difference between Group by and Partition by

In below articles I have used both group by and also partition by

• Difference between rank, dense_rank and row_number function in Oracle
• Total Scenario of Callerlog Table
• Finding Count of Outgoing and Incoming calls from a Caller Log table in Oracle

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