Use of sql Partition by clause together with case and count

Wherever you are trying to use aggregate functions 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(Class) over (partition by Class)
from StudentTable

The benefit 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 used 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 Class,
case count(Class) over (partition by Class)
when 1 then null
else Row_Number() over (partition by Class order by Class)
end,
StudentID,
StudentName
from StudentTable


The output will appear like this:

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

http://www.programmingsolution.net/sqlserver2005/sqlserver-solutions/partition-by.php

EXECUTE sp_executesql

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(Title)
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;

Here @SQLString is the dynamic query where you have 2 variables --- @level and @max_titleOUT. The variable @level is going into the dynamic query and @max_titleOUT is coming out from the dynamic query.

@ParamDefinition is the string where we must declare all the variables that will be used in the dynamic query. Look that the @max_titleOUT is set as output.

After that we have to mention which variable outside the dynamic query will give the value for @level and which variable outside the dynamic query will hold the value from @max_titleOUT. So we are assigning

@level = @IntVariable, @max_titleOUT=@max_title OUTPUT

Where @IntVariable and @max_title is the variable declared outside dynamic query. So @level will take the value from @IntVariable and will be used inside the dynamic query.
@max_title is the variable where we will get the value out from the dynamic query.

Pretty tuff huh, but no way. This is a very advanced feature of sql server. Hope you like this.