Sum accross multiple tables

[sheep43]

Hi,
I've got 27 tables all in the format edate(datetime),in(bigint),out(bigint). I want to achive a select that in a specified period for each edate value it will give me the sum of `in` and `out` across all of the tables.
I have used this query (with all of the tables in) that is generated through php doing a select to a lookup table to find the current tables to select from.

SELECT t1.edate AS slot,
( SUM(t1.in) + SUM(t2.in) + SUM(t3.in) + SUM(t4.in) + SUM(t5.in) + SUM(t6.in)) AS sum_in,
( SUM(t1.out) + SUM(t2.out) + SUM(t3.out) + SUM(t4.out) + SUM(t5.out) + SUM(t6.out)) AS sum_out 
FROM `set0001_bw` AS t1 
LEFT JOIN `set0002_bw` AS t2 USING(`timestamp`) 
LEFT JOIN `set0003_bw` AS t3 USING(`timestamp`) 
LEFT JOIN `set0004_bw` AS t4 USING(`timestamp`) 
LEFT JOIN `set0005_bw` AS t5 USING(`timestamp`) 
LEFT JOIN `set0006_bw` AS t6 USING(`timestamp`) 
WHERE (t1.edate >= '2003-10-18' AND t1.edate < '2003-11-04') 
OR (t2.edate >= '2003-10-18' AND t2.edate < '2003-11-04') 
OR (t3.edate >= '2003-10-18' AND t3.edate < '2003-11-04') 
OR (t4.edate >= '2003-10-18' AND t4.edate < '2003-11-04') 
OR (t5.edate >= '2003-10-18' AND t5.edate < '2003-11-04') 
OR (t6.edate >= '2003-10-18' AND t6.edate < '2003-11-04')
GROUP BY t1.edate;

which returns the right result as long as all of the tables have data in for that datetime. The problem is that there will be more tables added in the future so there will not always be data from each table for a date.
I don't have control over the tables so I cannot change the format, and this query was getting a bit big. Can anyone think of a better solution?
Thanks

Barney

 

[r937]

i sympathize with your situation, and i would like to have a few words with the person that designed your database

the best solution is one table instead of many

if that is no longer possible, then upgrade to mysql 4.0 and use UNION

your query with the JOINS is most inefficient, besides being difficult to maintain and potentially returning wrong results

a UNION query would like like this:

[tt]SELECT 't1' AS table_id
, t1.edate AS slot
, SUM(t1.in) AS sum_in
, SUM(t1.out) AS sum_out
FROM `set0001_bw`
WHERE t1.edate >= '2003-10-18'
AND t1.edate < '2003-11-04'
UNION ALL
SELECT 't2' AS table_id
, t2.edate AS slot
, SUM(t2.in) AS sum_in
, SUM(t2.out) AS sum_out
FROM `set0001_bw`
WHERE t2.edate >= '2003-10-18'
AND t2.edate < '2003-11-04'
UNION ALL
SELECT 't3' AS table_id
, t3.edate AS slot
, SUM(t3.in) AS sum_in
, SUM(t3.out) AS sum_out
FROM `set0001_bw`
WHERE t3.edate >= '2003-10-18'
AND t3.edate < '2003-11-04' [/tt]

this will give you one result row per table

rudy

http://r937.com/

 

[r937]

um, too fast with the cuttin & pastin...

change table name in the UNION to set0001_bw, set0002_bw, etc.


rudy