[thelist] Counting Nulls and Not-Nulls in Access (Solved)

[Tab Alleman]

Tab Alleman wrote:

> Say I've got a table like,
> IDENTITY	ProductID	DateSold
> 
> I want to get output like this:
> ProductID	TotalCount	QuantitySold	QuantityAvailable
> 
> Can I do this in a single query?  


Got it:

SELECT
	ProductID
, 	COUNT(*) AS TotalCount
,	(
	SELECT COUNT(*)
	FROM MyTable t2
	WHERE t2.DateSold IS NOT NULL
	) AS QuantitySold
, 	TotalCount - QuantitySold AS QuantityAvailable
FROM MyTable t1
GROUP BY ProductID

...hooray for subqueries.