Subtracting one SELECT COUNT() from another SELCT COUNT() on same -- sql server programming

a
8/27/2006 9:32:04 PM

I hope this is the correct place to post this.

I'm fairly inexperienced at SQL and I'm not even sure what to call this
query.

I have multiple records for each DateFiled
and some of the entries have a value in the FormXML column
but some entries in the FormXML column are NULL.

I'd like to first count ALL the entries for each DateFiled,
then next count the NULLs in the FormXML column, then find the difference
between these two Count() results and return them as a result in a single
query (Not 2 query sets)
like:

NumberOfForms NumberOfNulls Difference DateFiled
1256 5 1251 20060606
3256 15 3241 20060607

How can this be done?

Thanks
----------------------------------------------------------------------

SELECT
COUNT(ID)AS NumberOfForms,
DateFiled

FROM MyTable

WHERE DateFiled
BETWEEN ('20060606')
AND ('20060607')
AND (FormType = '4')

GROUP BY DateFiled
ORDER BY DateFiled

----------------------------------------------------

SELECT
COUNT(ID)AS NumberOfNulls,
DateFiled

FROM MyTable

WHERE DateFiled
BETWEEN ('20060606')
AND ('20060607')
AND (FormType = '4')
AND FormXML IS NULL

GROUP BY DateFiled
ORDER BY DateFiled

a
8/27/2006 11:08:02 PM

Thanks Tom, that's a nice neat solution.

Paul
----------------------------------------------------------

[quoted text, click to view]

a
8/27/2006 11:11:01 PM

Yea, I guess if I already knew what you were talking about I wouldn't have
even bothered asking in the first place
-----------------------------------------------------------------------------------------

[quoted text, click to view]

Dieter Noeth
8/28/2006 12:00:00 AM

[quoted text, click to view]

count(*)

[quoted text, click to view]

count(*) - count(FormXML)

[quoted text, click to view]

count(FormXML)

You're just trying to calculate the difference in the wrong place :-)

select
count(*) as NumberOfForms,
count(*) - count(FormXML) as NumberOfNulls,
count(FormXML) as Difference

[quoted text, click to view]

Tom Cooper
8/28/2006 12:41:39 AM

SELECT
COUNT(ID)AS NumberOfForms,
Sum (Case When FormXML Is Null Then 1 Else 0 End) As NumberOfNulls,
Sum (Case When FormXML Is Null Then 0 Else 1 End) As [Difference],
DateFiled

FROM MyTable

WHERE DateFiled
BETWEEN ('20060606')
AND ('20060607')
AND (FormType = '4')

GROUP BY DateFiled
ORDER BY DateFiled;

Tom

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Subtracting one SELECT COUNT() from another SELCT COUNT() on same