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
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.
So workaround is , you have to use the partition clause
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.