You will need to be using MySQL 4.1.x in order to perform sub-selects.
From: Steve Pugh
Sent: 4/12/04 11:01 AM
Subject: Multiple SELECTs in one query
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 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 =
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
totalFrames = rs!totalcount
SQLStr = " SELECT Count(*) AS DoneCount FROM " & flist & "
WHERE Status = 'Complete'"
doneframes = rs!donecount
SQLStr = "SELECT Avg(renderminutes) as AvgRenderTime FROM "
& flist & " WHERE Status = 'Complete'"
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
Many thanks for any help you can provide!
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql