Thursday, 30 January 2014

USING PARTITION CLAUSE FOR AGGREGATE FUNCTIONS

 



Are you looking for alternate of group by clause?
Consider the scenario, We have to fetch the count of birthday of all students in the particular dept along with the college_id field.
If college id field was not there then, we can use the following query

select dept_id,count(*) from profile where Bday=’08/29/1992’ group by Bday;

The above query will work fine but, If we want to include College_id field in the result_set the below query will return error. This is because group by is used for aggregate functions. Here For particular dept there may be several college_id’s. As like CSE is present in various colleges.

select college_ID,Dept_id,count(Bday) from profile where Bday=’08/29/1992’ group by Bday;  -- ERROR QUERY --

So workaround is , you have to use the partition clause

select college_ID,Dept_id,count(Bday) over (partition by dept_id) from profile where Bday=’08/29/1992’ group by Bday;  -- ERROR QUERY --

This will return the following output:

COLL_ID  DEPT_ID         COUNT
1               10                    5
2               12                    2
This shows that, at college 1 , in dept 10, 5 students have birthday at  01/29/1992      

Here the college id  and dept id should be primary.