only select count value if total row count is equal to row count with special ID

I need to select the the total number of rows with a certain ID IF it equals the total max rows in table.

I am using variables in this example, but I would like to do it with only one select statemen开发者_如何学编程t

DECLARE @a int, @b int

--total with special field (redcar = yes)
SELECT @a = (SELECT COUNT(*) FROM dbo.car WHERE redcar = 'yes') 

-- max total of table
SELECT @b = (SELECT COUNT(*) FROM dbo.car) 

IF(@a = @b)
BEGIN
SELECT @a
END

EXAMPLE dbo.car

id  redcar
1    yes
2    
3    yes
4
5
6

The output should be 0 because red car doesn't equal total row count of table

The fastest way I can think of:

SELECT COUNT(*)
FROM dbo.car
WHERE NOT EXISTS (SELECT 1 FROM dbo.CAR where COALESCE(redcar, '') <> 'yes')

Since you want to return all rows, this just checks if any rows don't meet your condition, and if so it should return 0 or nothing.

This should do the trick:

SELECT ISNULL(b.numrows, 0) as numrows
FROM (
    SELECT COUNT(*) AS numrows FROM car
) a
LEFT JOIN (
    SELECT COUNT(*) AS numrows FROM car WHERE redcar = 'yes'
) b ON b.numrows = a.numrows

I think you could do something like this:

SELECT count(*)
  FROM dbo.car
HAVING count(*) = sum (Case When redcar = 'yes' Then 1 Else 0 End)

or this:

SELECT allcars
  FROM (SELECT COUNT(*) redcars FROM dbo.car WHERE redcar = 'yes') as redcars
  cross join (SELECT COUNT(*) allcars FROM dbo.car) as allcars
 WHERE redcars = allcars

But why do you want one query? Doing with variables like your code is much more easy to read.

You could use a case to return 0 if the matched row count is different from the total number of rows:

SELECT  case when count(*) = (select count(*) from dbo.car) then count(*)
        else 0
        end
FROM    dbo.car 
WHERE   redcar = 'yes'

This will return one row with 0 if the condition is not met. The if statement would return no rowset at all. You might have to adjust the client code to account for this.