CODEWITHSUNDEEP
sql-servert-sql
jnasty
jnasty

Reputation: 79

COUNT in CASE - SQL Server

Is it possible to do a value assignment with a COUNT() as the when clause?

Like so:

SELECT @value = 
CASE 
 WHEN 
  COUNT(tableID) 
  FROM (SELECT TOP (5) tableID FROM table) AS id = 20 
 THEN 'Looks Good'
END

I basically what to select a variable amount of rows [TOP (@rowCount)], then take action based on the number of rows counted. I'm sure I can do this someway somehow, guessing I'm just missing something in the syntax.

Upvotes: 0

Views: 665

Answers (4)

Nizam
Nizam

Reputation: 4699

I think I understood your question. You want to know if it is possible to have TOP N rows of table, when N is variable. If I am right, you will need to specify a column which the table would be ordered.

Then you can use something like:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY COLUMN_NAME) TOPCOL
    FROM TABLE_NAME
) A
WHERE TOPCOL <= N

If I am not right, you should edit your question, because it is very hard to understand what you meant

Upvotes: 0

Cody M
Cody M

Reputation: 121

Assuming you are using at least sql2005 or greater then this will work - 

--create a table to test with
create table #TestTable
(
    TestTableID int primary key
)

--populate test table
declare @i int = 0;
while @i < 10
begin
    insert into #TestTable select @i;
    set @i = @i + 1;
end
GO

--now create variables to hold the TOP value and to store the result
declare @a int = 5
        ,@value varchar(10);

--correct case stmt syntax
set @value = case 
                when (select count(RecordList) as 'RecordListCount' from (select top (@a) TestTableID as 'RecordList' from #TestTable) as sq) = 20 then 'Looks Good'
            else 'Looks Bad'
        end;
select @value;

Remember to put the TOP variable in parentheses and to supply aliases for all of the tables and columns.

I hope that helps!

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40289

If you're looking for code branching, the following would work:

IF 20 = (select count(*)
          from (select top (5) tableID from table) as id)
    PRINT 'Looks Good'
ELSE
    PRINT '5 will never equal 20'

If you want to get or set a value, one of the following would work:

SELECT case count(*)
         when 20 then 'good'
         else 'bad'
       end
 from (select top (5) tableID from table) as id

or 

SELECT case
         when count(*) > 5 then 'Over 5'
         when count(*) < 5 then 'Under 5'
         else 'Exactly 5'
       end
 from (select top (5) tableID from table) as id

Upvotes: 2

Ian Kenney
Ian Kenney

Reputation: 6426

Not sure if I understand the question, but maybe try something like 

select @val = case when the_number  >= 20 then 'Looks good' end
from (
  select count(*) the_number from some_table 
) x

Upvotes: 0

Related Questions