You might consider a whole different approach which is more efficient, because
regardless of VB or MySQL - in your current setup you are issueing 3 distinct SQL
statements against the same table and you might be able to convert it to only issueing
one.
Basically, use functions to create 1's or 0's for every row of the table - depending
on the 3 conditions and then sum/average them up. For the count(*) just put a 1. For
the "complete" say "if complete then 1 else 0". For the 3rd select minutes or 0
depending on "complete".
The SQL will look something like this (I am writing in pseudocode):
SELECT sum (1) as "total_count",
sum (if status = "complete" then 1 else 0) as "count_complete",
avg (if status = "complete" then minutes else 0) as "avg_of_complete"
FROM tableFLIST
You'll get all 3 results in one pass.
>
> You will need to be using MySQL 4.1.x in order to perform sub-selects.
>
> -----Original Message-----
> From: Steve Pugh
> To: [EMAIL PROTECTED]
> Sent: 4/12/04 11:01 AM
> Subject: Multiple SELECTs in one query
>
> Hello, all!
>
> I am porting my Visual Basic app over from MSDE to MySQL, and things so
> far are going quite well. I've found most of the "gotcha" differences
> in how I need to structure my queries, but I am having trouble with one
> in particular.
>
> In my original code, I could use one query to get a total count of
> records, a count of records meeing a criteria (Status = "Complete"), and
>
> an average on another field for the records meeting that criteria. It
> looked like this in code:
>
> SQLStr = "SELECT DoneCount=(SELECT Count(*) FROM " & flist & " WHERE
> Status = 'Complete'), " & _
> "TotalCount=(SELECT Count(*) FROM " & flist & "), " & _
> "AvgRenderTime=(SELECT Avg(renderminutes) FROM " & flist
> & " WHERE Status = 'Complete')"
>
> The resulting SQL query would look something like this:
>
> SQLStr = "SELECT DoneCount=(SELECT Count(*) FROM tableFLIST WHERE Status
>
> = 'Complete'), TotalCount=(SELECT Count(*) FROM tableFLIST),
>
> AvgRenderTime=(SELECT Avg(renderminutes) FROM tableFLIST WHERE Status =
> 'Complete')
>
> Now, in MySQL, I get syntax errors in the query - most of them around
> "TotalCount=" in this example. In my investigation, I found that I
> could break the one query apart and execute three calls to get the
> information I needed, like this:
>
> SQLStr = "SELECT count(*) as TotalCount FROM " & flist
> rs.Open SQLStr
> totalFrames = rs!totalcount
> rs.Close
>
> SQLStr = " SELECT Count(*) AS DoneCount FROM " & flist & "
> WHERE Status = 'Complete'"
> rs.Open SQLStr
> doneframes = rs!donecount
> rs.Close
>
> SQLStr = "SELECT Avg(renderminutes) as AvgRenderTime FROM "
> & flist & " WHERE Status = 'Complete'"
> rs.Open SQLStr
> rs.Close
>
> So now that I've made a long story even longer, my question is simply
> this - is there a way to execute all three selects within the same
> query, as I was able to do when my database was MSDE? It seems that it
> would be more efficient than making three hits on the database when one
> would suffice.
>
> Many thanks for any help you can provide!
>
> Steve
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
