List:General Discussion« Previous MessageNext Message »
From:Udikarni Date:April 12 2004 9:35pm
Subject:Re: Multiple SELECTs in one query
View as plain text  
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: mysql@stripped
> 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/mysql?unsub=1
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>  http://lists.mysql.com/mysql?unsub=1
> 
> 
> 
>

Thread
Multiple SELECTs in one querySteve Pugh12 Apr
RE: Multiple SELECTs in one queryVictor Pendleton12 Apr
  • Re: Multiple SELECTs in one querySteve Pugh12 Apr
  • "INSERT INTO" dropping slashes from stringsSteve Pugh5 May
    • Re: "INSERT INTO" dropping slashes from stringsDaniel Clark5 May
      • Re: "INSERT INTO" dropping slashes from stringsSteve Pugh5 May
        • Re: "INSERT INTO" dropping slashes from stringsSteve Pugh5 May
          • Re: "INSERT INTO" dropping slashes from stringsDaniel Clark5 May
            • Re: "INSERT INTO" dropping slashes from stringsSteve Pugh5 May
              • Re: "INSERT INTO" dropping slashes from stringsDaniel Clark5 May
        • Re: "INSERT INTO" dropping slashes from stringsDaniel Clark5 May
    • Re: "INSERT INTO" dropping slashes from stringsDan Nelson5 May
      • Re: "INSERT INTO" dropping slashes from stringsSteve Pugh5 May
  • Re: "INSERT INTO" dropping slashes from stringsLou Olsten8 May
Re: Multiple SELECTs in one queryUdikarni12 Apr
  • Re: Multiple SELECTs in one querySteve Pugh14 Apr
  • Re: Multiple SELECTs in one queryHarald Fuchs14 Apr
Re: Multiple SELECTs in one queryUdikarni14 Apr
  • Re: Multiple SELECTs in one queryMichael Stassen14 Apr