how to reuse subquery result in mysql

Question

I'm doing a statistic job like this:

SUM    |COND1 |COND2 |...
--------------------------
100    |80    | 70   |...

The SUM result is calculated from multiple tables, and the CONDs are subset of that.

I wrote a sql like this:

select tmp1.id,sum,cond1,cond2  from (
   select id, count(*) as sum from table1 
   group by table1.id) tmp1
 left join ( 
   select id, count(*) as cond1 from table1
   where condition1
   group by table1.id) tmp2 on tmp1.id=tmp2.id
 left join ( 
   select id, count(*) as cond2 from table1
   where condition2
   group by table1.id) tmp3 on tmp1.id=tmp3.id

the problem is that this is very poor efficiency, it will be better if i could use the result of tmp1, but i don't know how to do that.

update: simplified the sql, the first subquery in this case:

select id, count(*) as sum from table1 
   group by table1.id) tmp1

is simpified , the real one is a pretty complex query, what i mean is how to reuse this nested select result when i calculate cond1 and cond2.

Answer 1

You should try to rewrite your query to do it all in only one table scan. Use the IF statement:

SELECT id, 
COUNT(*) AS sum, 
SUM( IF( condition1 , 1, 0 ) ) cond1, -- emulates a count where condition1
SUM( IF( condition2, 1, 0 ) ) cond2   -- same thing, for condition2
FROM table1
GROUP BY id

Post your table structure if you want the correct query written out :)