CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Narendra
Narendra

Reputation: 179

Count number of rows without using any aggregate functions

I need to count the number of rows in a table with a single SQL query.

That query should not contain any aggregate function like COUNT.

Upvotes: 8

Views: 32463

Answers (10)

ganesh kavhar
ganesh kavhar

Reputation: 33

COUNT THE NUMBER OF ROWS OF ITEMS.

SELECT MAX(EMPLOYEE_ID) FROM (SELECT ROW_NUMBER() OVER(ORDER BY EMPLOYEE_ID DESC) EMPLOYEE_ID FROM MYCODE);

Upvotes: 1

user2586782
user2586782

Reputation: 115

An over() function solves the problem.

select Column1,Column2, count(*) over() as testColumn from tablename

Upvotes: 6

Code Magician
Code Magician

Reputation: 23972

I'm a little surprised nobody has mentioned this yet:

EXEC sp_spaceused @objname = N'MyTable', @updateusage = 'TRUE'

This will, among other things return the current count of records in the table.

This would also work in most cases:

SELECT rows 
FROM sys.partitions 
WHERE index_id IN (1,0) 
    AND object_id = OBJECT_ID(N'MyTable')

Upvotes: 8

J0e3gan
J0e3gan

Reputation: 8938

Since "single SQL query" has proven to be a loose requirement, consider what I thought of immediately...but dismissed as unfit (as I indicated in comments on @AllenSHansen's answer):

SELECT 1 FROM YourTable;
SELECT @@ROWCOUNT;

Upvotes: 0

J0e3gan
J0e3gan

Reputation: 8938

The ROW_NUMBER ranking function will do the trick in a single query without using an aggregate function:

SELECT TOP 1 Row
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY SomeColumn) Row
    FROM YourTable) output
ORDER BY Row DESC

Upvotes: 5

Srikanth
Srikanth

Reputation: 14

Using ROW_NUMBER(), you will get the total number of rows in a table without using aggregate functions as the 2-step example below demonstrates.

1) Table & Data Setup

Example:

Create Table Dted (id int , name Varchar(20))

Insert into dted values (1,'aaa'),(2,'bbb'),(3,'ccc')

Select * from dted

Output:

id   name
1    aaa
2    bbb
3    ccc

2) Total Number of Rows

Example:

select top 1 ROW_NUMBER() over (order by id) as TotRows from dted order by TotRows desc

Output:

TotRows
3

Upvotes: -2

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

Easy way:

DECLARE @i INT = 0

SELECT @i = @i + 1
FROM <WHAT EVER>

SELECT @i AS RC

Fast, effective and no need for all sorts of sub queries as it requires only one run through the data :)

Upvotes: 4

arin1405
arin1405

Reputation: 677

Here you may find one way to do that:

SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'Mytable' AND (index_id < 2)

Find a good discussion on it here.

Upvotes: -1

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

There are to ways to count records in a table:

  1. Let the dbms count. You'd use an aggregate function for this, e.g. select count(*) from mytable;
  2. You count. I.e. execute select * from mytable; and then count the records shown.

I would suggest the former method, but you seem to want to use the latter. Enjoy.

Upvotes: -2

Chethan
Chethan

Reputation: 300

try this

SELECT MAX(Row) FROM 
(SELECT ROW_NUMBER() OVER(ORDER BY anycolumn DESC) Row FROM tablename) output

but what is stopping you use Count()? prefer to use count().

Upvotes: -1

Related Questions