CODEWITHSUNDEEP
mysqlsql
user1606540
user1606540

Reputation: 79

using inline select on temporary table

Iam using mysql in a reporting tool. I have a temporary table that Iam filling through some queries and then displaying data from the temporary table.

My table structure is 

Prim_key    starttime             endtime          timediff
1       2012-07-20 00:00:0    2012-07-22 10:55:32   39332
2       2012-07-20 04:50:13   2012-07-22 10:55:32   158719
3       2012-07-20 10:00:00   2012-07-22 00:00:05   5

I need to display a table with percentage of timings < 30 , between 30-45 and > 45. I have written the following query but i get error of Status 1137 - Can't reopen table: 't_results'

Expected result:

|   % of less than 30 min   | % of between 30 - 45 |    % of greater than 45 mins |

The query is 

SELECT prim_key key, (
  SELECT count(*)*100 / (
      SELECT count(*)
      FROM t_results 
      WHERE timediff > 0 )
  FROM   t_results 
  WHERE  (timediff /60)  < 30
  ) less30, (
  SELECT count(*)*100 / (
     SELECT count(*)
     FROM t_results 
     WHERE timediff > 0 )
  FROM   t_results 
  WHERE  (timediff /60)  >=  30  and (timediff /60)  <=  45 
  ) bet3045, 
  (SELECT count(*)*100 / (
      SELECT count(*)
      FROM t_results 
     WHERE timediff > 0 ) 
  FROM t_results 
  WHERE  (timediff /60)  > 45 
 )  great45 
FROM t_results 
WHERE 1 = 1 AND 1 =1 
GROUP BY prim_key

Is there another way of writing it ?

Upvotes: 0

Views: 735

Answers (2)

Ben Emery
Ben Emery

Reputation: 138

Temp tables cannot be refferred to twice within the same query.

http://bugs.mysql.com/bug.php?id=10327

EDIT

Actually, having a little think (as you require a pivot) I believe your query will be of the form

(second edit, formatting to be in percentages)

SET @numRows := (SELECT COUNT(*) FROM t_results WHERE timediff > 0 );
SELECT COUNT(CASE WHEN (timediff /60)  < 30 THEN timediff ELSE NUll END) * 100 /@numRows  less30, 
    COUNT(CASE WHEN (timediff /60)  >=  30  and (timediff /60)  <=  45  THEN timediff ELSE NUll END) * 100 /@numRows bet3045, 
    COUNT(CASE WHEN (timediff /60)  > 45  THEN timediff ELSE NUll END) * 100 /@numRows great45 
FROM t_results WHERE timediff > 0

Should do the trick. Unless I've made a rather silly mistake. Which I usually do :)

Upvotes: 2

Roland Bouman
Roland Bouman

Reputation: 31961

@user1051643 is right, you can't use temp tables more than once in a query. Pity, retarded etc, but that's how it is.

That said, what are you trying to achieve with your query? What strikes me as very odd is that your main query does a GROUP BY prim_key. Assuming that prim_key is the primary key, and your query has no joins, it seems that this is redundant since the conceptual primary key of the result of a GROUP BY query is always defined exactly by the column in the GROUP BY list. I.e. your GROUP BY is redundant.

Then in your SELECT list, you do a bunch of aggregate queries on t_results, but none of them seem correlated to the current row of the main query. This means you can easily and without any loss of performance rewrite it to a series of separate queries. But more than anything else it makes me think you haven't defined the problem you want to solve precisely enough.

UPDATE:

select 100 * sum(
                 if (timediff < 30*60, 1, 0)
             ) / count(timediff)  
       AS "% of less than 30 min"
,      100 * sum(
                 if (timediff between 30*60 and 45*60, 1, 0)
             ) / count(timediff)  
       AS "% of between 30 - 45"
,      100 * sum(
                 if (timediff > 45*60, 1, 0)
             ) / count(timediff) 
       AS "% of greater than 45 mins"
from   t_results

Upvotes: 1

Related Questions