Difference between Group by and Partition by

bashamsc's Avatar author of Difference between Group by and Partition by
Contributed by
This is an article on Difference between Group by and Partition by in Oracle.
Rated 5.00 By 1 users
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, shabbir likes this