Using subquery count to subtract from main query count

• DaveBriCam

  SSCarpal Tunnel

  Points: 4302

  More actions

  November 21, 2019 at 3:49 pm

  #3699672

  I'm trying to find the correct way of using a subquery count to subtract from main query count... thanks for any help!:

  SELECT count(p.[peopleId]) AS 'Inactives'
  FROM [BCC_DB].[dbo].[People] p 
  
  --MINUS
  
  (SELECT count(p.[peopleId]) AS 'Actives'
  FROM [BCC_DB].[dbo].[People] p
  INNER JOIN 
  [BCC_DB].[dbo].[certs] c 
  ON p.peopleId = c.peopleId
  WHERE p.status = 1 
  AND c.certificationDate Is Not Null 
  AND c.certStatusID = 3
  and p.ethicsflag = 0
  and p.ethicshold = 0)

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  November 21, 2019 at 4:01 pm

  #3699677

  SELECT count(c.people_id) AS 'Inactives' --<<-- note counting from *right* table
  FROM [BCC_DB].[dbo].[People] p
  LEFT OUTER JOIN --<<-- note *LEFT* join
  [BCC_DB].[dbo].[certs] c 
  ON p.peopleId = c.peopleId
  WHERE p.status = 1 
  AND c.certificationDate Is Not Null 
  AND c.certStatusID = 3
  and p.ethicsflag = 0
  and p.ethicshold = 0

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  November 21, 2019 at 4:09 pm

  #3699679

  I think this is a direct conversion of your SQL, just subtracting 2 inline SQL statements:

  SELECT (SELECT count(p.[peopleId])
            FROM [BCC_DB].[dbo].[People] p) 
       - (SELECT count(p.[peopleId])
            FROM [BCC_DB].[dbo].[People] p
           INNER JOIN [BCC_DB].[dbo].[certs] c 
                   ON p.peopleId = c.peopleId
                  AND c.certificationDate Is Not Null 
                  AND c.certStatusID = 3
           WHERE p.status = 1 
             AND p.ethicsflag = 0
             AND p.ethicshold = 0) AS Count
  

  This is probably more efficient than two inline selects, but I'm not sure how it will compare to Scott's answer.

  SELECT COUNT(p.[peopleId]) AS [Count]
    FROM [BCC_DB].[dbo].[People] p 
   WHERE NOT EXISTS(SELECT *
                      FROM [BCC_DB].[dbo].[certs] c 
                     WHERE c.peopleId = p.peopleId 
                       AND c.certificationDate Is Not Null 
                       AND c.certStatusID = 3
                       AND p.status = 1 
                       AND p.ethicsflag = 0
                       AND p.ethicshold = 0)

   

• DaveBriCam

  SSCarpal Tunnel

  Points: 4302

  More actions

  November 21, 2019 at 4:13 pm

  #3699682

  My problem is this part/count gives the correct figure of 4,090:

  SELECT count(p.[peopleId]) AS 'Inactives'
  FROM [BCC_DB].[dbo].[People] p

  And this gives the correct figure of 2,126:

  (SELECT count(p.[peopleId]) AS 'Actives'
  FROM [BCC_DB].[dbo].[People] p
  INNER JOIN 
  [BCC_DB].[dbo].[certs] c 
  ON p.peopleId = c.peopleId
  WHERE p.status = 1 
  AND c.certificationDate Is Not Null 
  AND c.certStatusID = 3
  and p.ethicsflag = 0
  and p.ethicshold = 0)

  And my difference should come out to 1,964

• DaveBriCam

  SSCarpal Tunnel

  Points: 4302

  More actions

  November 21, 2019 at 4:15 pm

  #3699683

  Thanks Jonathan it worked!!