A Simple Way to Sum a Result from Union in MySQL

A simple way to sum a result from UNION in MySQL

select id, sum(amount) from (
    select id,amount from table_1 union all
    select id,amount from table_2 union all
    select id,amount from table_3
) x group by id

MySQL: looking to SUM these UNIONs together

Use your entire query as the FROM clause of another query:

SELECT SUM(twitfollow) FROM (
    (SELECT mtwitterfollowers AS twitfollow FROM `media` WHERE media.id=1)
    UNION ALL
    (SELECT SUM(twitterfollowers) AS twitfollow FROM people LEFT JOIN peoplejoin ON peoplejoin.people_id = people.id LEFT JOIN positions ON position_id = positions.id WHERE peoplejoin.media_id = 1)
    UNION ALL
    (SELECT SUM(twitterfollowers) AS twitfollow FROM people LEFT JOIN peoplejoin ON peoplejoin.people_id = people.id LEFT JOIN networkjoin ON networkjoin.network_id = peoplejoin.network_id LEFT JOIN positions ON position_id = positions.id WHERE networkjoin.media_id = 1)
) t1

I also changed your UNION to UNION ALL as you probably don't want to remove rows just because the sum from one table is equal to the sum from another table.

SUM,COUNT with UNION in MySql

try this

 SELECT DATA ,CAUSE , TOT_TIME , N_RIPET_CAUSE
 FROM ( select DATA, CONCAT(`CAUSE_1`,' ',`CAUSE_2`, ' ', `CAUSE_3`) as CAUSE ,
 sum(`TIME_1` + `TIME_2` +`TIME_3`) as TOT_TIME ,
 (count(`CAUSE_1`)+count(`CAUSE_2`)+count(`CAUSE_3`)) as N_RIPET_CAUSE
 FROM your_table
 group by DATA
 ) t

SEE SQLFIDDLE DEMO

EDIT.

try this

     ( select DATA , `CAUSE_1` as CAUSE ,
     sum(`TIME_1` + `TIME_2` +`TIME_3`) as TOT_TIME ,
     (count(`CAUSE_1`)+count(`CAUSE_2`)+count(`CAUSE_3`)) as N_RIPET_CAUSE
     FROM Table1 
    group by DATA)
  union all
    (select DATA , `CAUSE_2` as CAUSE ,
    sum(`TIME_1` + `TIME_2` +`TIME_3`) as TOT_TIME ,
    (count(`CAUSE_1`)+count(`CAUSE_2`)+count(`CAUSE_3`)) as N_RIPET_CAUSE
    FROM Table1
    group by DATA   )
  union all

    (select DATA , `CAUSE_3` as CAUSE ,
    sum(`TIME_1` + `TIME_2` +`TIME_3`) as TOT_TIME ,
    (count(`CAUSE_1`)+count(`CAUSE_2`)+count(`CAUSE_3`)) as N_RIPET_CAUSE
    FROM Table1
    group by DATA   )

SQL DEMO HERE

EDIT:

try this due to your need

 select cause, sum(time) Tot_time, count(cause) N_Ripet_Cause
 from(
    select  cause_1 as cause, time_1 as time
    from Table1
    union all
    select  cause_2 as cause, time_2 as time
   from Table1
   union all
   select  cause_3 as cause, time_3 as time
   from Table1
 ) t
 group by cause

DEMO SQL FIDDLE

SQL sum rows in union

You don't actually need to use a UNION for this - you can just add them together manually:

SELECT a.countKey + b.sumOccur as total
FROM (SELECT COUNT(DISINCT clientkey) as countKey
      FROM <table>
      WHERE <conditions>) as a
CROSS JOIN (SELECT SUM(occurrences) as sumOccur
            FROM <table2>
            WHERE <conditions>) as b

Using SUM with UNION

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.

How to get sum of a UNION ALL

A possible solution with only mysql could be the following, which returns a single value of the summation of all three columns in the tables:

SELECT SUM(pt.psum) AS tsum FROM 
(SELECT SUM(money) AS psum FROM table1 WHERE money > 0 GROUP BY id
UNION ALL
SELECT SUM(payment) AS psum FROM table2 WHERE money > 0 GROUP BY id
UNION ALL
SELECT SUM(pay) AS psum FROM table3 WHERE money > 0 GROUP BY id) pt

For clarity,

• psum: partial sum
• pt: partial sum table
• tsum: total sum

How to sum multiple fields from a UNION Query in MySQL?

You can use a select with sum and group by based on the result from union

select  FieldOne
    , FieldTwo 
    , FieldThree 
    , sum(CountOne )
    , sum(CountTwo )
    , sum(CountThree )
from ( 
          Select FieldOne
          , FieldTwo 
          , FieldThree 
          , CountOne 
          , CountTwo 
          , CountThree 
      FROM my_view1
      UNION ALL
      Select FieldOne
          , FieldTwo 
          , FieldThree 
          , CountOne 
          , CountTwo 
          , CountThree 
      FROM my_view2
  ) T 
group by  FieldOne
    , FieldTwo 
    , FieldThree 
order by FieldOne

You can use UNION if you want merge distinct value or UNION ALL if you want merge all the result form the united select ..

and do the your "bit confusion " about the use for query in union subselect

select  FieldOne
    , FieldTwo 
    , FieldThree 
    , sum(CountOne )
    , sum(CountTwo )
    , sum(CountThree )
from ( 
          Select FieldOne
          , FieldTwo 
          , FieldThree 
          , CountOne 
          , CountTwo 
          , CountThree 
      FROM ( select col1 as FieldOne, col2 as FieldTwo .....
             from   ...  
             where 

         ) TT_A
      UNION ALL
      Select FieldOne
          , FieldTwo 
          , FieldThree 
          , CountOne 
          , CountTwo 
          , CountThree 
      FROM ( select col1 as FieldOne, col2 as FieldTwo .....
             from   ...  
             where 

         ) TT_B
  ) T 
group by  FieldOne
    , FieldTwo 
    , FieldThree 
order by FieldOne

How to sum up the results of a MySQL query using UNION

SELECT agent, SUM(`sum`) AS sum
FROM ( SELECT agent, SUM(p.vergoeding_partner) as sum 
       FROM Sales s
       INNER JOIN Proposities p ON s.product_name = p.naam
       WHERE finalized_at > CURDATE() AND flow=165
       GROUP BY agent
     UNION
       SELECT agent, SUM(IF(eancode_e<>'' AND eancode_g<>'',p.vergoeding_partner*2, p.vergoeding_partner)) as sum 
       FROM Transactions t
       INNER JOIN Proposities p ON t.productnaam = p.naam
       WHERE finalized_datetime > CURDATE()
       GROUP BY agent
     ) total
GROUP BY agent

How to add the results of two different queries with UNION

Your union query would end up giving you two records, one record per sum. Because you are selecting only a single value, you can omit the GROUP BY clause. I used the SUM() function and added it to a similar statement using an inline subquery.

SELECT SUM(H) + (SELECT SUM(H) FROM my_career_stats)
FROM stats_2017

---

Related Topics