Record count error? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Record count error?

Author

Topic

brad_x81
Starting Member

6 Posts

Posted - 2014-11-24 : 11:07:08

Hi,

I'm trying to get a record count by State and Country.

But it's giving a weird result - can someone check this code please or advise where I'm going wrong?

select

a.Primary_State__c,
a.Primary_County__c,
count (distinct a.unique_id__c) as [Record Count]

from

vwaccount a
LEFT JOIN RecordType rt (nolock) on a.RecordtypeID = rt.ID

where
a.Region__c in ('NA') and
a.Name in ('customer','formalized prospect','suspect','prospect')
and
(a.D_B_of_Employees__c <= '10000' or a.Employee_Count__c <= '10000')
group by
a.Primary_State__c,a.Primary_County__c

Result =

Primary_State__c Primary_County__c Record Count
NY NULL 1

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-24 : 11:10:45

what's weird? You have one matching row that meets the conditions.

Also, get rid of NOLOCK. It is deprecated. Use READ UNCOMMITTED if you can tolerate dirty reads or READPAST if you can't.

brad_x81
Starting Member

6 Posts

Posted - 2014-11-24 : 11:13:40

No there should be 1,000's per state.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-24 : 11:22:24

Well, what I would do is examine the data more closely. Build a query that looks for rows that would go into the aggregation, but leave out the COUNT() function and the GROUP BY. I'd start with no WHERE conditions then add them in one at a time and observe the results. Once I was sure that I had the conditions correct, I'd add in the COUNT() and GROUP BY.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources