CODEWITHSUNDEEP
sql-server-2008
Aada
Aada

Reputation: 1640

How to count number of records as well get the records from the query?

I have 3 tables A,B and C. In the stored procedure,I have used a query to get the result but i also want the total number of records i got from the above query. Is this possible. I tried using something like this

Select count(*) 
from (
   select A.Name,B.Address,C.grade 
   from A,B,C 
   where A.id=B.id 
   AND B.Tlno=C.tlno
)

But this is not working.

Upvotes: 0

Views: 1045

Answers (4)

Arpit
Arpit

Reputation: 126

use a table variable as below

    declare @num table (accname varchar(200),subnet varchar(200))
    insert into @num(accname,subnet) Select a.accountname,s.subnet from tbl_accounts  a,tbl_accountsubnet s where a.accountid=s.accountid
    select COUNT(*) from @num;

Upvotes: 0

pleinolijf
pleinolijf

Reputation: 921

Alternative would be to use the @@rowcount keyword:

SELECT A.Name, B.Address, C.grade, @@rowcount
FROM dbo.A
INNER JOIN dbo.B ON A.id = B.id
INNER JOIN dbo.C ON B.Tlno = C.tlno;

Same result as the windowing function though, so you get the total count on each row. I'm curious if there is a performance difference between the two... (don't have SHOWPLAN permission at my current client unfortunately)

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

(1) stop using old-style x,y,z joins.

SELECT A.Name,B.Address,C.grade 
  FROM dbo.A
  INNER JOIN dbo.B ON A.id = B.id
  INNER JOIN dbo.C ON B.Tlno = C.tlno;

(2) you can add a count(*) over() to the entire resultset. This is kind of wasteful because it returns the count on every row:

SELECT A.Name, B.Address, C.grade, row_count = COUNT(*) OVER ()
  FROM dbo.A
  INNER JOIN dbo.B ON A.id = B.id
  INNER JOIN dbo.C ON B.Tlno = C.tlno;

Upvotes: 3

user330315
user330315

Reputation:

You can use a windowing function:

select A.Name,
       B.Address,
       C.grade,
       count(*) over () as total_count
from A,B,C 
where A.id=B.id 
AND B.Tlno=C.tlno

this will return the total count in each and every row though (but it will be the same number for all rows).

Upvotes: 0

Related Questions