November 21, 2019 at 3:49 pm
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)
November 21, 2019 at 4:01 pm
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".
November 21, 2019 at 4:09 pm
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)
November 21, 2019 at 4:13 pm
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
November 21, 2019 at 4:15 pm
Thanks Jonathan it worked!!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy