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

create PROCEDURE Insert_Top as
 
   /*
 
   CREATE TABLE [dbo].[top](
      [SPID] [float] NULL,
      [Status] [nvarchar](255) NULL,
      [Login] [nvarchar](255) NULL,
      [HostName] [nvarchar](255) NULL,
      [BlkBy] [nvarchar](255) NULL,
      [DBName] [nvarchar](255) NULL,
      [Command] [nvarchar](255) NULL,
      [CPUTime] [float] NULL,
      [DiskIO] [float] NULL,
      [LastBatch] [nvarchar](255) NULL,
      [ProgramName] [nvarchar](255) NULL,
      [SPID1] [float] NULL,
      [REQUESTID] [float] NULL,
      [ModifiedDate] [datetime] NULL CONSTRAINT [DF_top_ModifiedDate]  DEFAULT (getdate())
   ) ON [PRIMARY]
 
   */
 
 
   INSERT INTO [DPart_P008_Dev].[dbo].[top]
             (
      [SPID],
      [Status],
      [Login],
      [HostName],
      [BlkBy],
      [DBName],
      [Command],
      [CPUTime],
      [DiskIO],
      [LastBatch],
      [ProgramName],
      [SPID1],
      [REQUESTID])
    EXEC sp_who2

Now, create the loop procedure:

    create PROCEDURE Loop_Insert_Top as
    WHILE (1=1) BEGIN
        EXEC insert_top
        WAITFOR DELAY '00:01:00';
    END

To view the records:

   select * from [top]