Count

Not sure if this is possible, but what I need from the following stored procedure is a list of all the ethnic categories
that havent had any contacts... The ethnic code is simply letter such as A,B,C but what I need are the counts of contacts for clients in particular ethnic categories

for example

WHITE 0 Contacts
ASIAN 0 Contacts
BLACK 1


SELECT     Contact_Record.client_id, Ethnic_grp.Ethnic_category
FROM         client INNER JOIN
                      Contact_Record ON client.id = Contact_Record.client_id INNER JOIN
                      Ethnic_grp ON client.ethnic_origin = Ethnic_grp.Code

Is this possible, if so can someone show me how its done...
paulo111Asked:
Who is Participating?
 
Maciej PileckiDatabase ArchitectCommented:
SELECT    Ethnic_grp.Ethnic_category, COUNT(Contact_Record.client_id)
FROM       client LEFT JOIN
                Contact_Record ON client.id = Contact_Record.client_id INNER JOIN
                Ethnic_grp ON client.ethnic_origin = Ethnic_grp.Code
GROUP BY ALL Ethnic_grp.Ethnic_category
HAVING COUNT(Contact_Record.client_id)=0  --this line is optional to get only those with 0 contacts
0
 
HilaireCommented:
Another option is

SELECT Ethnic_category FROM Ethnic_grp EG
WHERE NOT EXISTS(
      select 1 from FROM Contact_Record CR INNER JOIN client C ON C.id = CR.client_id
      WHERE C.ethnic_origin = EG.Code
)
0
 
rafranciscoCommented:
Try this:

SELECT A.Ethnic_Category, COUNT(*)
FROM Ethnic_grp A LEFT OUTER JOIN Client B
    ON A.Code = B.Ethnic_Origin
GROUP BY A.Ethnic_Category
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.