Aggregate functions and Subqueries? 1

[VBAjedi]

BACKGROUND: I'm trying to learn how to use aggregate functions like COUNT and SUM in subqueries, and still running into issues. My current challenge:

SETUP: I have table "PositionPlacements" containing fields "ID" (an AutoIncrement index), "PositionNumber" (alphanumeric), "EmployeeID"(alphanumeric), and "PositionType" (alphanumeric). (Yes, I am aware that having PositionType in this table makes it non-normalized! But that's another topic.) Position Numbers may appear two or more times in the dataset (with different EmployeeID's assigned).

QUESTION: I need a query that will return a COUNT (i.e. single row result set) of all rows where PositionType = "Permanent", minus the count of distinct PositionNumbers from those rows (where PositionType = "Permanent"). It's basically a count of how many people have been stacked on top of occupied positions, so by subtracting the count of unique position numbers I'm left with the duplicate placements.

I could show you what I've tried so far with nested queries, but those queries aren't working and probably aren't close to the right answer...

VBAjedi

 

[PHV]

You wanted this ?

SELECT COUNT(*) - (SELECT COUNT(*) FROM (SELECT DISTINCT PositionNumber FROM PositionPlacements WHERE PositionType = 'Permanent') D)
FROM PositionPlacements
WHERE PositionType = 'Permanent'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VBAjedi]

Beautiful, PH. A lot more elegant-looking than my efforts, too. When I grow up I want to be like you! ;-)

Have a star...

VBAjedi