SQL Join & Count

Question

I have two tables, one of carers and another of patients in MS SQL Server. Each patient is assigned a number of carers

╔════════════════╦══════════════════╦════════════════════╦═════════════════╗
║ Patient Name   ║  Primary Carer   ║  Secondary Carer   ║  Tertiary Carer ║
╠════════════════╬══════════════════╬════════════════════╬═════════════════╣
║ Joe Bloggs     ║  John Smith      ║  Oscar Wild        ║  Tom Cruise     ║
║ Mary Sue       ║  John Smith      ║  Matt Smith        ║  Peter Pan      ║
║ Peter Parker   ║  John Smith      ║  Oscar Wild        ║  Matt Smith     ║
╚════════════════╩══════════════════╩════════════════════╩═════════════════╝ 

I'm looking to select all records from my carers table, then count how many times that carer is a Primary, secondary or tertiary carer in the patients table including those carers who aren't assigned to anyone.

For example;

╔═══════════════╦═══════════════════════╦═════════════════════════╦═══════════════════╗
║ Carer         ║  Primary Carer Freq   ║  Secondary Carer Freq   ║  Third Carer Freq ║
╠═══════════════╬═══════════════════════╬═════════════════════════╬═══════════════════╣
║ John Smith    ║                   3   ║                     0   ║                 0 ║
║ Oscar Wilde   ║                   0   ║                     2   ║                 0 ║
║ Tom Cruise    ║                   0   ║                     0   ║                 1 ║
║ Matt Smith    ║                   0   ║                     1   ║                 0 ║
║ Peter Pan     ║                   0   ║                     0   ║                 1 ║
║ Barry White   ║                   0   ║                     0   ║                 0 ║
╚═══════════════╩═══════════════════════╩═════════════════════════╩═══════════════════╝ 

Thanks for any help!

Answer 1

You can use:

SqlFiddleDemo

SELECT 
   [name]
  ,[Primary Carer Freq]   = COUNT(p1.[Primary Carer])
  ,[Secondary Carer Freq] = COUNT(p2.[Secondary Carer]) 
  ,[Third Carer Freq]     = COUNT(p3.[Tertiary Carer]) 
FROM carers c 
LEFT JOIN  patients p1
  ON p1.[Primary Carer] = c.[Name]
LEFT JOIN  patients p2
  ON p2.[Secondary Carer] = c.[Name]
LEFT JOIN  patients p3
  ON p3.[Tertiary Carer] = c.[Name]
GROUP BY [name];

Data:

CREATE TABLE  patients([Patient Name] VARCHAR(100),
                       [Primary Carer] VARCHAR(100),
                       [Secondary Carer] VARCHAR(100),
                       [Tertiary Carer] VARCHAR(100) );


INSERT INTO patients
SELECT 'Joe Bloggs',      'John Smith',   'Oscar Wild',   'Tom Cruise' 
UNION ALL SELECT 'Mary Sue',    'John Smith',     'Matt Smith',   'Peter Pan' 
UNION ALL SELECT ' Peter Parker',   'John Smith',     'Oscar Wild',   'Matt Smith';

CREATE TABLE carers(Name VARCHAR(100));

INSERT INTO carers(Name)
SELECT 'John Smith'   
UNION ALL SELECT 'Oscar Wilde' 
UNION ALL SELECT 'Tom Cruise' 
UNION ALL SELECT 'Matt Smith'   
UNION ALL SELECT 'Peter Pan'
UNION ALL SELECT 'Barry White';

EDIT:

If you want combined result use +:

SELECT 
  [name]
  ,[Primary&Secondary Carer Freq]   = COUNT(p1.[Primary Carer]) + COUNT(p2.[Secondary Carer]) 
  ,[Third Carer Freq]               = COUNT(p3.[Tertiary Carer]) 

Answer 2

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Test_Table(PatientName VARCHAR(20), PrimaryCarer VARCHAR(20),
                       SecondaryCarer VARCHAR(20),TertiaryCarer VARCHAR(20));

INSERT INTO  Test_Table VALUES                       
  ('Joe Bloggs'   ,     'John Smith'   ,      'Oscar Wild'     ,      'Tom Cruise'),      
  ('Mary Sue'     ,     'John Smith'   ,      'Matt Smith'     ,      'Peter Pan' ),      
  ('Peter Parker' ,     'John Smith'   ,      'Oscar Wild'     ,      'Matt Smith');

Query 1:

SELECT CarerName
      ,COUNT(CASE WHEN  CarerType = 'PrimaryCarer'   THEN 1 END) AS PrimaryCarer
      ,COUNT(CASE WHEN  CarerType = 'SecondaryCarer' THEN 1 END) AS SecondaryCarer
      ,COUNT(CASE WHEN  CarerType = 'TertiaryCarer'  THEN 1 END) AS TertiaryCarer 
FROM  Test_Table T 
 UNPIVOT (CarerName FOR CarerType  IN(PrimaryCarer,SecondaryCarer,TertiaryCarer)
         )up 
GROUP BY CarerName

Results:

|  CarerName | PrimaryCarer | SecondaryCarer | TertiaryCarer |
|------------|--------------|----------------|---------------|
| John Smith |            3 |              0 |             0 |
| Matt Smith |            0 |              1 |             1 |
| Oscar Wild |            0 |              2 |             0 |
|  Peter Pan |            0 |              0 |             1 |
| Tom Cruise |            0 |              0 |             1 |