Grouping data from multiple tables

Question

Hi,

I have 4 tables for each month; oct, dec, feb, apr. They have the same schema.

I need to extract the data from all the tables using the same query and grouping it for one attribute.

I know how to do it for one table but I would like to combine the results into one query and then dump the output into the file.

Here is my sample query:

select TIMESTAMPDIFF(hour,startTime,endTime) as duration, count(*) from feb 
where type='gen' group by duration 
into outfile "/feb";

Since I am grouping this for duration attribute, I end up 4 different output files and then merging and sorting the data explicitly is something I would like avoid.

Any help would be appreciated!

Omer

Answer 1

You could use a UNION:

select TIMESTAMPDIFF(hour,startTime,endTime) as duration, count(*) from feb 
where type='gen' group by duration 
UNION
select TIMESTAMPDIFF(hour,startTime,endTime) as duration, count(*) from apr
where type='gen' group by duration 
UNION
select TIMESTAMPDIFF(hour,startTime,endTime) as duration, count(*) from oct 
where type='gen' group by duration 
UNION
select TIMESTAMPDIFF(hour,startTime,endTime) as duration, count(*) from dec 
where type='gen' group by duration 
into outfile "/all";

Note that you only use the INFO OUTFILE clause at the very end and not on each SELECT statement.

Answer 2

Use UNION ALL. It offers better performance than UNION, and it would be wrong to remove duplicates in this case anyway. You can also perform the GROUP BY after putting all of the tables together, so that it is possible for rows to be grouped across tables. That'll give you a count of rows with the same duration added up across all 4 tables.

select everything.duration, count(*)
from
((select TIMESTAMPDIFF(hour,startTime,endTime) as duration
  from feb where type='gen')
 union all
 (select TIMESTAMPDIFF(hour,startTime,endTime) as duration
  from apr where type='gen')
 union all
 (select TIMESTAMPDIFF(hour,startTime,endTime) as duration
  from oct where type='gen')
 union all
 (select TIMESTAMPDIFF(hour,startTime,endTime) as duration
  from dec where type='gen')) everything
group by everything.duration
into outfile "/all";