* chetana bhargav <[EMAIL PROTECTED]> [2006-12-29 17:20]:
> Actually I am trying to retrieve values in a single step.
>
> My queries need to be something like,
>
> select count(*) from tbl1 where state='Normal';select count(*) from tbl1
> where state='Critical'
>
> I got to have these two as seperate, because if there's any
> critical need to display a diff icon, and also the sum of those
> results. So wondering how can I avoid two table scans, and
> instead try to retrieve them in a single statement.
Then you should ask how to do that, instead of just making up a
way you think it should work and then asking whether that’s
possible.
What you want can be done by using an expression that returns
some non-NULL value for rows you want to include in the count and
NULL value for those you’re not interested in, then counting the
rows you got.
SELECT
COUNT( CASE state WHEN 'Normal' THEN 1 ELSE NULL END ) AS num_normal,
COUNT( CASE state WHEN 'Critical' THEN 1 ELSE NULL END ) AS num_critical
FROM
tbl1
Regards,
--
Aristotle Pagaltzis // <http://plasmasturm.org/>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
