Using subquery count to subtract from main query count

  • 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)
  • 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".

  • 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)

     

  • 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

  • Thanks Jonathan it worked!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply