SQL Server Performance Forum – Threads Archive

count(*) giving difference in result

count(ID) giving a change in the number of records. the ID column is indexed and sometimes it gives lesser number of records than the prevous result.

---

Seehttp://toponewithties.blogspot.com/2004/08/let-us-count-them.html Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

---

hi, the ID column is a not null column
and i’m using joins from tables in different databases. what makes the count differ

---

hi, the ID column is a not null column
and i’m using joins from tables in different databases. what makes the count differ

---

Can you post some sample statements you’re using along with the row count returned? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

If you are using left, right or full outer join then there are chances that there can be null.. As suggestted by Frank, post what are you trying to do.. Chirag

---

hi, the query with multiple joins which i’m trying to use is given below. the Total for some groups keep changing where the only changes happens to TMaster is this a index related problem because database maintanence was happening ************ SELECT A.ID, max(A.name)as Aname ,max(DB2.dbo.code.codename)as db2codename,count(A.ID)AS Total
FROM TMaster Trans
INNER JOIN
DB2.dbo.Company Comp ON CONVERT(varchar, Trans.CompanyNo) = CONVERT(varchar, Comp.CompanyNo)
INNER JOIN
TForm A ON Trans.ID =A.ID
INNER JOIN
DB2.dbo.code ON CAST(Comp.compcode AS int) = CAST(DB2.dbo.code.DetailedCode AS int) WHERE
(Comp.compcode IS NOT NULL) AND (DB2.dbo.code.MCode = ‘016’)
AND (Trans.CustomerID =
(SELECT top 1 CustomerID
FROM T_Customer
WHERE (CustomerTypeID = 1)and (Trans.CustomerID=T_Customer.CustomerID)))
AND (Trans.SubmitDate >= CONVERT(datetime, ‘1/3/2005’, 103))
AND (Trans.SubmitDate <= CONVERT(datetime,’1/4/2006′, 103))
AND (Comp.compcode = ‘1’)
GROUP BYA.ID
order by Aname

---

]]>