Question

我试图从两张工作桌上抽取,我挣扎!

我用笔记本、一张与接触链接的表格、一张与客户联系的表格和一个连接客户地址的表格。我试图做些什么是,发现每个员额代码有多少次由联系人和客户分类的邮件。

Postcode | Count of Clients | Count of Contacts    
UB | 123 | 12345    
HA | 223 | 22345

但是,显然只有接触者和客户得到同样的重视。当我总是错过错时,在试图将它列入一些空洞的声明时,我感到可怕的困惑。

select COUNT (ni.NotebookItemId) AS Clients, 
COUNT(nl.ObjectId) AS Contacts, 
Substring(ad.PostCode, 1, 2) AS Postcode
from NotebookItems ni
inner join notebooklinks nl on ni.NotebookItemId = nl.NotebookItemId
inner join ClientContacts cc on cc.ContactPersonId = nl.ObjectId
inner join address ad on ad.ObjectId = cc.ClientId
where NotebookTypeId = 75 AND ni.CreatedOn >  mar 16 2012  
AND (ad.postcode like  UB%  or 
     ad.postcode like  HA% )
GROUP BY Substring(ad.PostCode, 1, 2)
order by Contacts desc

Answer 1

Count() will count all non null rows, as Andrew says you might be after: COUNT(DISTINCT thing) i.e.:

select COUNT (DISTINCT ni.NotebookItemId) AS Clients, 
COUNT(DISTINCT nl.ObjectId) AS Contacts, 
Substring(ad.PostCode, 1, 2) AS Postcode
...

然后,你将得出每个领域两个不同的价值观。

I m not sure this is what you want. You say:

What I m trying to do is find out how many times each postcode has been mailshotted grouped by contact and client.

我期望问一下这方面的数据:

SELECT 
      Count(MailshotID) AS Count
    , Substring(address.PostCode, 1, 2) AS Postcode
    , ContactID
    , ClientID
FROM
    -- your joined tables here
WHERE
    -- your criteria here
GROUP BY 
      Substring(address.PostCode, 1, 2)
    , ContactID
    , ClientID

获得:

Count   Postcode   ContactID  ClientID
3       UB         201        301
6       HA         202        302
2       UB         203        305
18      UB         203        306

每个员额代码、联系人、客户或按邮政编码、联系人和客户分类的邮件数。

友情链接