Counting Values based on certain criteria SQL

Question

I am trying to Write a View for some reporting in our CRM program and I'm having trouble with the counts. We are trying to count the number of referrals based on certain criteria. I feel like the way I wrote the view is incorrect. I am trying to use a CASE statement to make sure it counts the values that I want, but I the numbers I am getting I am not able to validate with reverse engineering.

Did I structure the CASE statement correctly?

(Note: I do realize that client is misspelled, unfortunately whoever wrote the database before I got it had some spelling issues.)

This is what I have written so far:

SELECT Comp_Name, Count(Case WHEN comp_primaryreferralsource IS NOT NULL and Comp_type = 'Client' Then 1
                        ELSE NULL END) AS Client_Referrals,
      Count(Case WHEN lead_companyprimaryreferralsource IS NOT NULL Then 1 Else Null End) AS Target_Referrals,
      Count(Case WHEN comp_primaryreferralsource IS NOT NULL and Comp_type = 'Prospect' Then 1 Else Null End) As Prospective_Client_Referral, 
      Count(Case WHEN comp_primaryreferralsource IS NOT NULL and Comp_type = 'Lost_Clent' Then 1 Else Null End) AS Lost_Client_Referral,
      Count(Case WHEN mcgr_companyid IS NOT NULL Then 1 Else NULL END) AS Intro_By_MCG

FROM Company

RIGHT JOIN Lead on Lead_PrimaryCompanyID = Comp_CompanyId
RIGHT JOIN MCGRelationships on mcgr_companyid = Comp_CompanyId
WHERE COMP_Name IS NOT NULL

Group By Comp_Name

Here is a Sample of What i'm getting:

  Client1      0    0   0   0   1
  Client2      0    0   0   0   2
  Client3      0    0   0   0   1
  Client4      0    0   0   0   1
  Client5      0    0   0   0   2
  Client6      0    0   0   0   2
  Client7      0    0   4   0   4
  Client8      0    0   0   0   2
  Client9      0    2   2   0   2
  Client10     12   6   0   0   12
  Client11     0    0   0   0   2

When I just run a query on the table where Client 10 is the Primary Referral source, I get nothing. So I can't account for those 12 instances.

What tables do those fields derive? You don't use not table qualifiers. Also, what happens if you rewrite query for `LEFT JOIN` and if you changed `COUNT()` to `SUM()`? — Parfait, Jan 15 '17 at 05:26
Brent, @TimBiegeleisen 's request for "sample data" does not mean an extract of what you're currently getting as results: it means an ***example of possible data*** in the `Company`, `Lead` and `MCGRelationships` tables; and what you would expect your query result to be given that sample data. — Disillusioned, Jan 15 '17 at 06:17
What you're doing is [pivoting](http://stackoverflow.com/questions/tagged/sql-server+pivot?sort=active&pageSize=50) your data. You should rather use the pivot features of sql server as per answers in the linked questions. But if you're using a particularly old version and have to do the pivot "manually": please see a [previous answer](http://stackoverflow.com/a/1997581/224704) of mine demonstrating how to do so correctly. (Use `SUM()` instead of `COUNT()`, an I'd say the `ELSE` part of `CASE` should rather return `0` instead of `NULL`.) — Disillusioned, Jan 15 '17 at 06:38

score 0 · Answer 1 · answered Jan 15 '17 at 05:24

I think your problem may be in the join to MCGRelationships, if each company record can have more than one relationship record. If I'm following you correctly, you want Intro_By_MCG to be 1 if that record exists in MCGRelationships, regardless of the number of times it appears there. By joining the table, your query would be counting a lead once for each MCGRelationship associated with the company the lead comes from. Try this and let us know if the result is different:

SELECT Comp_Name, Count(Case WHEN comp_primaryreferralsource IS NOT NULL and Comp_type = 'Client' Then 1
                    ELSE NULL END) AS Client_Referrals,
  Count(Case WHEN lead_companyprimaryreferralsource IS NOT NULL Then 1 Else Null End) AS Target_Referrals,
  Count(Case WHEN comp_primaryreferralsource IS NOT NULL and Comp_type = 'Prospect' Then 1 Else Null End) As Prospective_Client_Referral, 
  Count(Case WHEN comp_primaryreferralsource IS NOT NULL and Comp_type = 'Lost_Clent' Then 1 Else Null End) AS Lost_Client_Referral,
  Count(Case WHEN EXISTS (Select Comp_CompanyId FROM MCGRelationships WHERE mcgr_companyid = Comp_CompanyId) Then 1 Else NULL END) AS Intro_By_MCG

FROM Company

RIGHT JOIN Lead on Lead_PrimaryCompanyID = Comp_CompanyId
WHERE COMP_Name IS NOT NULL

Group By Comp_Name

Problem is they are different instances. One is clients referred to us by them and one is intros we made. The relationships table only houses those in which we've made an introduction, so I would need to keep that join in for that data. — Brent Spears, Jan 15 '17 at 05:34

Counting Values based on certain criteria SQL

1 Answers1