Boolean Logic in Select Clause

Evidentally, I can't do this:

SELECT 
  SUM( t.MyField IS NULL ) AS totalNulls, 
  SUM( t.MyField LIKE '[0-9]') AS totalNumbers
FROM MyTable AS t;

I don't know why these don't work, since booleans in SQL are just numbers (0 and 1). But the errors I'm getting suggest it is not legal to have 'is null' or 'l开发者_StackOverflowike' anywhere in the select clause. Why aren't they legal there? How do I achieve the intended effect, as suggested by the (pseudo) SQL above?

SQL Server has no idea what a boolean is. BIT <> boolean, though it is a common misconception. You also can't do control-of-flow things in a SELECT statement (e.g. IF). In the next version of SQL Server, we are getting inline IIF() functionality, which kind of does what you're looking for. In the meantime, you'll need to use a CASE expression.

SELECT
  totalNulls =   SUM(CASE WHEN t.MyField IS NULL      THEN 1 ELSE 0 END),
  totalNumbers = SUM(CASE WHEN t.MyField LIKE '[0-9]' THEN 1 ELSE 0 END)
FROM MyTable AS t;

If your data isn't indexed on the queried column you should use the CASE-based solution Aaron recommended because the rows will only be queried once.

select  totalNulls = sum(case when MyField is null then 1 else 0 end),
        totalNumbers = sum(case when MyField like '[0-9]' then 1 else 0 end)
from    MyTable;

You should know that you only accept e.g. '7' as a number not '12' in this case. If you want to accept any numbers you'd have to ask

... when MyField not like '%[^0-9]%' ...

In this case you could do a charm using isnumeric:

select  totalNonNumbers = count(*) - sum(isnumeric(MyField)),
        totalNumbers = sum(isnumeric(MyField))
from    MyTable;

If the column is indexed a variation of Michaels solution could be fastest:

select  totalNulls = (select count(*) from MyTable where MyField is null),
        totalNumbers = 
          (select count(*) from MyTable where MyField between '0' and '9');

There are so many ways ...

SELECT SUM(CASE WHEN t.MyField IS NULL THEN 1 ELSE 0 END) as TotalNulls

I don't think comparison operators return 1 and 0, I think they're handled differently, but I'm not sure on that point, however the sum on the case statement will work and you can do it in one pass on as many columns/calculations as you like.

One way would be:

SELECT 
  count(*) AS totalNulls, 
  0 as totalNumbers
FROM MyTable AS t
where t.MyField is null
union 
SELECT 
  0 AS totalNulls, 
  count(t.MyField) AS totalNumbers
FROM MyTable AS t
where t.MyField like '[0-9]';

The first row will contain the null count, the second row will contain the total count of t.MyField that contains digits.

One more way...

declare @t table (
  id int null
)
insert into @t
select 1
union
select null
union
select 2

SELECT
  totalNulls =   COUNT(*)-COUNT(ID),
  totalNumbers = SUM(CASE WHEN ISNUMERIC(t.id)=1 THEN 1 ELSE 0 END)
FROM @t AS t;