[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Count

Posted on 2005-04-20
3
Medium Priority
?
333 Views
Last Modified: 2010-03-19
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...
0
Comment
Question by:paulo111
3 Comments
 
LVL 6

Accepted Solution

by:
graf0 earned 1500 total points
ID: 13823486
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 13824106
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
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13824277
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question