How to sum fields across multiple tables in mysql?

Question

I have the following query:

SELECT adate, sum(b), sum(c), sum(d)
FROM
(
  (
    SELECT a.adate, sum(b), sum(c), sum(d)
    FROM one
    WHERE a='aa'
    GROUP BY a.adate
  )
  UNION ALL 
  (
    SELECT a.adate, sum(b), sum(c), sum(d)
    FROM two
    WHERE a='aa'
    GROUP BY a.adate
  )
  UNION ALL
  (
    SELECT a.adate, sum(b), sum(c), sum(d)
    FROM three
    WHERE a='aa'
    GROUP BY a.adate
  ) 
  UNION ALL 
  (
    SELECT a.adate, sum(b), sum(c), sum(d)
    FROM four
    WHERE a='aa'
    GROUP BY a.adate
  )
) a
GROUP BY a.adate

I basically want to sum fields across four tables.

Is this a good query to do this?
Is there a better way of doing that?

Put all four tables into one table, as it represents the same kind of information, and add another column to that table to reference to `('one','two','three','four')`. This way your database is well structured and retrieving data is easy. — wolfgangwalther, Oct 31 '14 at 03:47
To pay the statistics have four tables with different statistics. Tell us more efficient queries than union all — KMJ, Oct 31 '14 at 03:56
Currently riding a lot, and even though the index is the amount of data that the query is slow and continue to see a trend. — KMJ, Oct 31 '14 at 04:23
You could try to remove all the `GROUP BY`s and `SUM`s in the inner queries and just group and sum once on the outer query. Don't know whether that will be any more efficient, though. But at least a couple of mistakes would be eliminated (referencing a.adate in the inner query doesn't make any sense, does it?) — wolfgangwalther, Oct 31 '14 at 04:23
Well that's going to happen with every query. The more data you have, the slower the query will be. Again: Put all four tables in one table and that should help you increase performance. — wolfgangwalther, Oct 31 '14 at 04:59

score 0 · Answer 1 · edited May 23 '17 at 12:27

As proposed, change your query to:

SELECT adate, sum(b), sum(c), sum(d)
FROM
(
  (
    SELECT *
    FROM one
    WHERE a='aa'
  )
  UNION ALL 
  (
    SELECT *
    FROM two
    WHERE a='aa'
  )
  UNION ALL
  (
    SELECT *
    FROM three
    WHERE a='aa'
  ) 
  UNION ALL 
  (
    SELECT *
    FROM four
    WHERE a='aa'
  )
) a
GROUP BY adate

This groups and sums just once. See Getting the sum of several columns from two tables for a similar problem and solution.

But as already proposed in the comments: You should seriously think about changing your table structure, because you have four tables with identical structure, which can be merged into one table with an additional column to set to the name of the previous four tables.

How to sum fields across multiple tables in mysql?

1 Answers1