CODEWITHSUNDEEP
mysqlsqlsumunion
Rick Barr
Rick Barr

Reputation: 3

Using SUM with UNION

I've been looking into this all day, trying all kinds of solutions, but just can't get anything to work. I don't think it's all that complicated. I have two tables that have common fields, one of which is 'jobamount'. The two tables are 'LiveMusic' and 'VO'. I'm just trying to get the total of all the values of each table using UNION. So, my basic statement right now is:

SELECT SUM(jobamount) FROM LiveMusic WHERE jobdate LIKE '2019%'
UNION ALL
SELECT SUM(jobamount) FROM VO WHERE jobdate LIKE '2019%'

This works fine as far as, I'm getting two results, the sum of all the values from each table. How do I SUM these, GROUP them, whatever? I just need to combine these into one. Thanks much!

Upvotes: 0

Views: 324

Answers (3)

Ali Ghaini
Ali Ghaini

Reputation: 905

you can use subquery:

SELECT SUM(X.jobamount) 
  FROM (
        SELECT jobamount FROM LiveMusic WHERE jobdate LIKE '2019%'
        UNION ALL
        SELECT jobamount FROM VO        WHERE jobdate LIKE '2019%'
       ) X

Upvotes: 2

daiwenlong
daiwenlong

Reputation: 11

like this..

SELECT
    SUM(amount_sum)
FROM
    (
        SELECT
            SUM(jobamount) AS amount_sum
        FROM
            LiveMusic
        WHERE
            jobdate LIKE '2019%'
        UNION ALL
            SELECT
                SUM(jobamount) AS amount_sum
            FROM
                VO
            WHERE
                jobdate LIKE '2019%'
    ) t

Upvotes: 1

Ladi Oyeleye
Ladi Oyeleye

Reputation: 154

You could sum the sums:

SELECT SUM(jobsum) AS totalsum FROM (
SELECT SUM(jobamount) AS jobsum FROM LiveMusic WHERE jobdate LIKE '2019%' UNION ALL SELECT SUM(jobamount) AS jobsum FROM VO WHERE jobdate LIKE '2019%'
) sums;

Or sum the values after union all:

SELECT SUM(jobamount) AS totalsum FROM (
SELECT jobamount FROM LiveMusic WHERE jobdate LIKE '2019%' UNION ALL SELECT jobamount AS jobsum FROM VO WHERE jobdate LIKE '2019%'
) jobamounts;

You can run an EXPLAIN on both queries to see which one runs faster for you.

Upvotes: 2

Related Questions