How Can I Count the Rrows Using ROW_NUMBER()?!

Question

Post reply

How Can I Count the Rrows Using ROW_NUMBER()?!

NimBus_87

Valued Member

Points: 58
More actions
May 19, 2010 at 10:13 am

#232670

Hi All,
i want to count the number of row using this Select statement below, which did work for me, but did not return what i want, this is what it did return:
RowNumber
1
2
3
4
5
i want it to return the count of rows, which will be (5) for this case. so could someone please tell me how can i modify the code below so it will return the following:
RowNumber
5
Thanks, and looking forward to hearing from you
SELECTROW_NUMBER() OVER (order by tblA.AID) AS 'RowNumber'
FROM tblA INNER JOIN tblB ON tblA.AID = tblB.AID
WHERE tblB.Date Between '2008/01/01' And '2008/01/05'
GROUP BY tblA.AID
HAVING Count(tblB.B) > 3

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

This should do it for you.

with countrow as (SELECTROW_NUMBER() OVER (order by tblA.AID) AS 'RowNumber'

FROM tblA INNER JOIN tblB ON tblA.AID = tblB.AID

WHERE tblB.Date Between '2008/01/01' And '2008/01/05'

GROUP BY tblA.AID

HAVING Count(tblB.B) > 3)

Select Max(RowNumber)

From countrow

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

NimBus_87 Valued Member Points: 58 More actions · Answer 2

Thanks CirquedeSQLeil for your reply,,

i know i could do it the way you just mentioned, the thing that i want it to be in one stored procedure.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

The method I showed can be done in a single stored procedure.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

NimBus_87 Valued Member Points: 58 More actions · Answer 4

oo Ya ,,,

when i saw your first reply, i thought you were telling me to create it into 2 SP, now i tested what you had posted and it did work ,, Thanks again CirquedeSQLeil 🙂

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events