assign count value

Hi all,

How I can assign a COUNT result to a variable in store procedure like this:

Code Snippet

SELECT @.NumCalc=COUNT(*) AS NC
FROM (SELECT COUNT(*) AS N
FROM Trace
GROUP BY Ora) TraceTmp

thanks a lot

by hid

Hi all sorry but

SELECT @.NumCalc=COUNT(*) /*AS NC */
FROM (SELECT COUNT(*) AS N
FROM Trace
GROUP BY Ora) TraceTmp

Run !

Bye Bye Hid

|||

If you want save into @.NumCalc result of SELECT COUNT(*) AS N FROM Trace GROUP BY Ora, you could use following code:

Code Snippet

SELECT @.NumCalc= COUNT(*)

FROM #Trace

GROUP BY Ora

But if SELECT COUNT(*) AS N FROM Trace GROUP BY Ora returns more than one line, only last result will be saved in @.NumCalc.

If you want save into @.NumCalc number of lines returned by SELECT COUNT(*) AS N FROM Trace GROUP BY Ora, you could use following:

Code Snippet

select @.NumCalc = count(distinct ora) from #trace

|||

Are you getting an error message returned? There's actually nothing wrong with your query - as long as you declare @.NumCalc then it should work fine.

Incidentally, your query is equivalent to this:

SELECT @.NumCalc = COUNT(DISTINCT Ora)

FROM Trace

...which, personally, I find easier to read.

Chris

|||

You code will not execute BECAUSE you cannot BOTH assign a aggregrate to a variable AND give it an ALIAS.

@.NumCalc=COUNT(*) AS NC

Remove the [ AS NC ] and it will execute just fine.

However, it would be easier to read if written as:

Code Snippet

SELECT @.NumCalc = count(DISTINCT Ora)
FROM Trace

|||

Arnie,

I see that this solution have been recommended several times. It will work just if column [Ora] does not accept NULL.

Code Snippet

select

count(distinct c1)

from

(

select null as c1

union all

select 1

) as t

select

count(*)

from

(

select

count(*) as cnt

from

(

select null as c1

union all

select 1

) as t1

group by

c1

) as t2

AMB

|||

'Hunchback'

I guess I didn't get your point. The OP asked for help with [ SELECT count(*) ]. In giving him/her the 'benefit of the doubt', I assume that if NULL is not to be included in the count, the OP will later clarify.

And yes, you are correct, there were previous variations of the same suggestion. I was not offering anything new EXCEPT for an explanition about why the OP's code excerpt would not work. (Actually, one post told the OP that there was "nothing wrong with your query", when in fact, it just will not execute as presented.

|||

Arnie,

> I assume that if NULL is not to be included in the count, the OP will later clarify.

The OP is counting the number of rows produced by a grouping by [Ora], so changing it to count(distinct [Ora]) could not produce the same result y [Ora] allows NULL. That was my point and was reproduced with the attached script.

AMB

|||Thanks for the clarification -I overlooked that point. I appreciate that you corrected my mistake.|||

Arnie said:

'Actually, one post told the OP that there was "nothing wrong with your query", when in fact, it just will not execute as presented.'

Yes, that was me. It seems that the OP had found a solution and subsequently corrected their query in a follow-up post, to which I was replying. The second query has the alias commented out so it will work as posted and, therefore, my statement is correct.

Chris

|||

Yes, I agree. Yet, it did seem confusing since due to forum latency it first appeared to me that you were responding to the original post. Even after the thread caught up, it was difficult to determine which post you responded to.

I should not have included that comment -it didn't add any value to the discussion. My regrets.