help with calculation where fields are varchar

[thorny00]

The fields are varchar, and I'm trying to do a calculation.
How is this done?
The field contains either a 0, 1 or null
Need to count the total all of values, then calc. the percentage of 0's, 1's and null's against the total.

Thanks for your help and time, it's greatly appreciated!!

 

[checkai]

select count(case when field is null then 1 else 0 end) as 'NULLS',
count(case when field = '0' then 1 else 0 end) as 'ZEROS',
count(case when field = '1' then 1 else 0 end) as 'ONES'

From table

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[genomon]

If your field will pass the ISNUMERIC test, you can CAST as an integer (using ISNULL(field,0).....

 

[vongrunt]

Count is simple:

select somefield, count(*) as cnt from blah group by somefield

Percentage thing requires one extra pass to get total:

select A.somefield, cast(100.0*A.cnt/B.totalcnt as decimal(5, 2)) as pct from 
(	select somefield, count(*) as cnt from blah group by somefield ) A,
(	select count(*) as totalcnt from blah ) B

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)