[SOLVED] Doubt with a Select

[rantsh]

Hi all,

 

I want to do a select with 2 counts of the same column, one of them should be where column1>0 and the other has to be a count regardless of the value.

 

Anyone can tell me if this is possible?

 

Thx,

 

Roderick

[fenway]

It's a little tricky... I've cheated before with SUM(), basically using a 0 or 1 to fake the counts for the conditional one.

[rantsh]

someone at the mysql forum posted a reply to this same Q (I asked both here and there)

 

the reply was

select
count(column1) allrows,
count case when column1>0 then 1 else null end as somerows
from yourtablename

 

Has someone tried this out before? I'll try in a while and let you know though

[fenway]

Sure, that will work just as well... I was thinking something along the lines of:

 

select
count(column1) allrows,
SUM( IF(column1>0,1,0 ) ) as somerows
from yourtablename

 

 

[rantsh]

I must say both solutions work like a charm, thanks for your help (and to Guelphdad from the mysql forum)

[artacus]

Doubt with a Select

 

I think the doubt clause has been deprecated since version 5.0.4