Saturday, April 4, 2009

Use of sql Partition by clause together with case and count


Wherever you are trying to use aggregate funct
ions in sql server, it will give you error like that a group by clause is necessary. Yes, if you really want to group by, then ok. But what about if you want to see the count of a group but you want to every elements of that group. Group by wont allow you to see other columns that what is specified in the group by clause. But you can. Here is the tips and tricks.


select Class, StudentID, StudentName, count(Clas
s) over (partition by Class)
from StudentTable

The bene
fit of partition by is that you can use partition by for generating as many columns as you want and you can specify a different partition criteria for every column.

See below how partition by is use
d together with the case statement. Here one single column is generated but it is using two different partition by clause, one to determine the case condition and the other is generating the row number value.

select Cla
ss,
case count(Class) over (partition by Class)
when 1 th
en null
else Row_Number() over (partition
by Class order by Class)
end,
StudentID,
StudentName
from Stud
entTable


The output
will appear like this:











You can find some other useful cases where partition by can be used in the following article.

No comments: