can we combine these 3 statements into one single query

SELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp

SELECT 1 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL

SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.idSELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp
UNION ALL
SELECT 2 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL
UNION ALL
SELECT 3 as id, (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id|||The above query doesn't work for me and infact i want to get the percentage in a single query..
Thanks|||I'm "winging" this one wildly, but could you use:SELECT
CAST(Sum(CASE WHEN name IS NOT NULL
AND name <> '' THEN 1 END) AS FLOAT) / Count(*)
FROM dbo.empThis divides the number of names with value by the total to get the percentage of usable names.

-PatP|||How about

SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM (
SELECT COUNT(name) as count1
INTO #temp1
FROM emp
JOIN
SELECT COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX|||SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM (
SELECT COUNT(name) as count1
INTO #temp1
FROM emp
JOIN
SELECT COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX
........................

I could not execute the above query, can we write the query like that?
please correct me if i am wrong?|||Don't use Name <> NULL

No value will ever match that:

MyField > NULL will always be false
MyField < NULL will always be false
MyField <> NULL will always be false
MyField = NULL will always be false
and so on for all applicable operators...

try using:

NULLIF(Name, TRIM(Name)) IS NOT NULL

to catch fields containing only spaces.|||try using:

NULLIF(Name, TRIM(Name)) IS NOT NULL

to catch fields containing only spaces.

My bad. Use this instead:

NULLIF('', TRIM(Name)) IS NOT NULL|||Just curious, but didn't my suggestion work? What results did it produce?

-PatP|||Replace:
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX
By:
WHERE LTRIM(ISNULL(name,'')) <> '' ) AS XXX

yabu.