[sqlite] Re: multiple selects in a single prepare

[A. Pagaltzis]

* Igor Tandetnik <[EMAIL PROTECTED]> [2006-12-29 17:35]:
> Why not
> 
> select state, count(*)
> where state in ('Normal', 'Critical')
> group by state;
> 
> ?

Clever!


* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-12-29 17:40]:
> SELECT count(state='Normal'), count(state='Critical') FROM tbl1;

How exactly does this work? I assume it involves data type
coercion, but what are the rules and effects?

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: multiple selects in a single prepare

[Igor Tandetnik]

[EMAIL PROTECTED] wrote:

SELECT count(state='Normal'), count(state='Critical') FROM tbl1;


Wouldn't that just return the number of all rows in tbl1, twice? That 
probably should be


SELECT sum(state='Normal'), sum(state='Critical') FROM tbl1;

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: multiple selects in a single prepare

[A. Pagaltzis]

* 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 // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: multiple selects in a single prepare

[chetana bhargav]

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.

..
Chetana

- Original Message 
From: A. Pagaltzis <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 28, 2006 11:21:28 AM
Subject: [sqlite] Re: multiple selects in a single prepare


* chetana bhargav <[EMAIL PROTECTED]> [2006-12-28 06:00]:
> Just wanted to know can we have multiple quries in a single
> prepare statement seperated by semicolons.Something like,
> 
> Select count(*) from tbl where name="foo";select count(*) from tbl1 where 
> name = "bar"

Just how is that supposed to work?

Are you looking for the UNION operator, perchance?

Regards,
-- 
Aristotle Pagaltzis // <http://plasmasturm.org/>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

[sqlite] Re: multiple selects in a single prepare

[A. Pagaltzis]

* chetana bhargav <[EMAIL PROTECTED]> [2006-12-28 06:00]:
> Just wanted to know can we have multiple quries in a single
> prepare statement seperated by semicolons.Something like,
> 
> Select count(*) from tbl where name="foo";select count(*) from tbl1 where 
> name = "bar"

Just how is that supposed to work?

Are you looking for the UNION operator, perchance?

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: multiple selects in a single prepare

[Igor Tandetnik]

chetana bhargav
 wrote:

Just wanted to know can we have multiple quries in a single prepare
statement seperated by semicolons.Something like,

Select count(*) from tbl where name="foo";select count(*) from tbl1
where name = "bar"


sqlite3_prepare will parse a single statement, and return a pointer to 
the first character of the next statement. You would need to call it 
again to prepare the next statement.


Note also that string literals should be enclosed in single quotes, as 
in   name = 'foo'. SQLite tolerates double quotes but sometimes this may 
lead to surprising effects.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-