How to List TOP 'N' Rows along with a Count using SQL Server

I recently came across a requirement where the user had to list the details of TOP 5 rows that matched a particular critera, but also had to list the total no rows that matched that criteria.

Here's how it can be solved. I am using the Customers table of the Northwind database:

SELECT TOP 5 CustomerID, CompanyName,
COUNT(*) OVER () AS TotalSimilarTitles
FROM Northwind.dbo.Customers
WHERE ContactTitle = 'Owner'

In the query over here, I list the TOP 5 Customers who have the ContactTitle as 'Owner'. The query also counts the total number of Customers that have the ContactTitle as 'Owner'
at

1 comment:

  1. sandySeptember 2, 2009 at 12:23 AM

    SELECT a.CustomerID, a.CompanyName,(Select Count(*)from
    (select TOP (5) * from Northwind.dbo.Customers
    WHERE CustomerID = a.CustomerID AND ContactTitle = 'Owner') as TotalSimilarTitles,COUNT(*)
    FROM Northwind.dbo.Customers a
    group by a.CustomerID

    ReplyDelete