SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

download SQL Server 2016
download SQL Server 2014
download SQL Server 2012

SQL COUNT() and ROW_NUMBER() Function with PARTITION BY for 1 of n Items

This SQL tutorial demonstrates usage of SQL COUNT() and SQL ROW_NUMBER() function to select a row with its order number and total number of rows in a group in the format "1 of n" items, like first page of a total 3 pages, 1/3, 1 of 3, etc.

In order to get the order number, SQL Window function Row_Number() which is introduced with SQL Server 2005 SQL development enhancements is used. For total number in the desired category SQL aggregate function COUNT() is used with PARTITION BY clause which is also available since SQL Server 2005.

Partition By enables a grouping according to the list of columns given in the Partition By clause. SQL Window functions like Row_Number(), Rank(), Dense_Rank(), Tile(), NTile() and aggregate functions like SUM(), COUNT(), AVEGARE(), MAX(), MIN(), etc provides data valid within that partition. This provides SQL developers code less sql lines without the use of temporary tables and better performance with build-in grouping and partitioning support by SQL Server engine.

We will now create a SQL SELECT statement which brings an image data and its number within total number of images in the related gallery. You can expect a result like "1 of 2 images", "1 / 3", "2 of total 5", etc.

Let's create our sample database tables and populate them with sample data. SQL developers can execute below script which creates Gallery table and Images table. Images table has a foreign key column GalleryId (unfortunately I did not create the foreign key constraint here) which is a reference to Gallery entity.

 Id int identity(1,1),
 [Description] nvarchar(1000)

insert into Gallery select 'Database'
insert into Gallery select 'SQL Server'
insert into Gallery select 'Business Intelligence'

 ImageId int identity(1,1),
 GalleryId int,
 [Name] nvarchar(1000),
 Path varchar(1000)

insert into Images select 1, N'Database Design', '/database-design.png'
insert into Images select 2, N'SQL Server 2014', '/sqlserver2014.png'
insert into Images select 3, N'SSRS', '/ssrs.png'
insert into Images select 2, N'In-memory OLTP', '/in-memory-oltp.jpg'
insert into Images select 1, N'What is RDBMS?', '/rdbms.png'
insert into Images select 1, N'OLAP vs OLTP', '/olap-oltp.jpg'

Now we will create a single SQL query with a SELECT statement but will be using SQL Window function ROW_NUMBER() and SQL Aggregate function COUNT() with PARTITION BY enhancement.

Row_Number() function will get the sort order of the related data row within in its category defined in the Partition By clause

SQL Count() function will return the number of rows (or images) within the grouped set of images based on gallery (galleryid) with the extension Partition By clause.

 [Name] as [Image],
 [Description] as Gallery,
 ROW_NUMBER() over (partition by GalleryId order by ImageId) rn,
 COUNT(*) OVER (partition by GalleryId) cnt,
 [n of Total m images] =
 cast( (ROW_NUMBER() over (partition by GalleryId order by ImageId)) as varchar(5))
 + ' of ' +
 cast( (COUNT(*) OVER (partition by GalleryId)) as varchar(5)) + ' images'
from Images i
inner join Gallery g on g.Id = i.GalleryId

Here is the output of the above Transact-SQL Select statement.
Coding with using the new sql functions and enhancements eanbles developers to get the desired values with less code.

For example, in this senario the developers had to use SubSelect statements for the number of images in each gallery. Or at least they have to store the count of images in each gallery into a temp table and join this temp table with Images table in the Select statement for a similar result.

Nth row of total m rows in a group in SQL Server query
List items using SQL query with their numbers in a group in the "n of m" format

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums

Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems