Do I need a data subset? - 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

General SQL Server Forums

New to SQL Server Programming

Do I need a data subset?

Author

Topic

brad_81x
Starting Member

8 Posts

Posted - 2014-01-29 : 10:26:56

Im trying to get counts of contacts.

So how do I subset, to gain a the accounts with the products then query to gain the contact counts from those accounts?

But for some reason it's giving me counts of products, please help!!

select distinct

a.region__c as [Region],
a.SFTX_ID as [SFTRX ID],
count(c.Contact_ID) as [Contact Count]

from vwContact c

join vwaccount a (nolock) on c.accountid = a.id
inner JOIN vwInstalled_Product_Line__c IP (Nolock) on a.id = ip.Account__c
inner join dbo.RecordType rt (nolock) on a.RecordtypeID = rt.ID

where
ip.Active_Flag__c in ('true')
and a.Maintenance_Status__c in ('active')
and Product_Line__c in ('Product')
and rt.name in ('Customer')
and c.active__c in ('true')

group by a.region__c,a.SFTRX_ID,c.Contact_ID
order by 2

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-29 : 11:49:04

if you want contact count then you probably want to remove contact_id from the group by clause. And because you seem to need to include your products table then you likely need to count distinct contact_id. Maybe this:


select a.region__c as [Region],
       a.SFTX_ID as [SFTRX ID],
       count(distinct c.Contact_ID) as [Contact Count]

from vwContact c
join vwaccount a (nolock) on c.accountid = a.id
inner JOIN vwInstalled_Product_Line__c IP (Nolock) on a.id = ip.Account__c
inner join dbo.RecordType rt (nolock) on a.RecordtypeID = rt.ID

where  ip.Active_Flag__c in ('true')
and a.Maintenance_Status__c in ('active')
and Product_Line__c in ('Product')
and rt.name in ('Customer')
and c.active__c in ('true')

group by a.region__c,a.SFTRX_ID
order by 2

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources