asked on

Counting number of row, number of rows with a null in colx and the number of rows without a null in colx.

Hi.

Am I missing anything by the snippet below.

Is summing the only way to count the values (not the distinct values)

SELECT
		@i_StartedWith = COUNT(*),
		@i_Dead = SUM(CASE WHEN CRR.DateRemoved IS NULL THEN 1 ELSE 0 END),
		@i_Live = SUM(CASE WHEN CRR.DateRemoved IS NULL THEN 0 ELSE 1 END)
	FROM
		dbo.CachedRemovalReasons CRR

Open in new window

tigin44

try this
SELECT
@i_StartedWith = COUNT(*),
@i_Dead = SUM(CASE WHEN CRR.DateRemoved IS NULL THEN 1 ELSE 0 END),
@i_Live = SUM(CASE WHEN CRR.DateRemoved IS not NULL THEN 1 ELSE 0 END)
FROM
dbo.CachedRemovalReasons CRR

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

shru_0409

SELECT COUNT(*) as i_StartedWith,
            count(CASE WHEN CRR.DateRemoved IS NULL THEN 1 ELSE 0 END) l_dead,
            count(CASE WHEN CRR.DateRemoved IS not NULL THEN 1 ELSE 1 END) i_Live
      FROM dbo.CachedRemovalReasons CRR

try his

Richard Quadling

ASKER

Thanks all, though I am shocked, dismayed and appalled that only tigin44 realized I was assigning the values to some variables and not outputting them in a select statement!Gee - call yourselves experts!!!

Qlemo

That does not make a big difference - it was the figure, to show alternatives. And I did realize you assign the values to vars - but it was totally unimportant. You already seemed to be able to do that yourself.

Did you realize that tigin44 did not answer your question at all? Your query is correct, and the slight variation he supplied does not change anything to that.

Richard Quadling

ASKER

You're right. Doh. I thought I saw something useful there. It was my own work - nearly ...

Brain dead. Busy. Cycled to work. Exhausted.

Need coffee.

Home time soon please ...