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

Question

I'm currently learning how to use MySQL in queries and I have a dilemma.

Currently, I have 2 queries that fetch data from multiple tables. Both queries contain the same number of column fields and field names.

For simplicity, the output of the queries looks something like this:

QueryOne:

   FieldOne| FieldTwo | FieldThree | CountOne | CountTwo | CountThree 
    data1  |  data11  |  data111   |    3     |     0    |      0
    data2  |  data22  |  data222   |    0     |     1    |      0
    data3  |  data33  |  data333   |    0     |     0    |      2
    data4  |  data44  |  data444   |    0     |     4    |      0
    data5  |  data55  |  data444   |    0     |     0    |      1

QueryTwo:

   FieldOne| FieldTwo | FieldThree | CountOne | CountTwo | CountThree 
    data1  |  data11  |  data111   |    1     |     0    |      0
    data1  |  data11  |  data111   |    0     |     0    |      2
    data6  |  data66  |  data666   |    0     |     5    |      0

Both queries contain the same data for FieldOne-FieldThree, but may contain different values for CountOne-CountThree.

There will never be an instance where the data will contain values in all 3 fields CountOne, CountTwo, CountThree, but only in ONE of those 3 fields.

What I'm trying to do is create a separate UNION QUERY that has the following output:

   FieldOne| FieldTwo | FieldThree | CountOne | CountTwo | CountThree 
    data1  |  data11  |  data111   |    4     |     0    |      0
    data1  |  data11  |  data111   |    0     |     0    |      2
    data2  |  data22  |  data222   |    0     |     1    |      0
    data3  |  data33  |  data333   |    0     |     0    |      2
    data4  |  data44  |  data444   |    0     |     4    |      0
    data5  |  data55  |  data444   |    0     |     0    |      1
    data6  |  data66  |  data666   |    0     |     5    |      0

Basically, if there is more than one instance of the same data in FieldOne, and there is a value in the same CountOne, CountTwo, or CountThree for multiple instance of that same data, I'd like to get the SUM of that field and group that data by FieldOne.

However, if there is more than one instance of the same data, but it contains a value in a different Count field, then the query should create a separate record for it, like in the example, data1 contains values in both CountOne and CountTwo fields.

I did something like this in SQL View of Access 2013 with a UNION QUERY:

SELECT *
FROM [QueryOne]
ORDER BY FieldOne
UNION SELECT *
FROM [QueryTwo]
ORDER BY FieldTwo;

However, with my limited knowledge, I'm stuck on how to incorporate what I want into the above code in one go, instead of creating another separate query.

Access seems like a strange environment within which to learn MySQL — Strawberry, Mar 25 '18 at 08:19

ScaisEdge · Accepted Answer · 2018-03-25T07:42:15.560

3

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

edited Mar 25 '18 at 07:42

answered Mar 25 '18 at 07:00

ScaisEdge

131,976
10
91
107

sorry to ask, but this all can be done in `SQL View` of Access? – Pangu Mar 25 '18 at 07:09
why not .. instead of my_table1 adn my_table2 just use your view_name – ScaisEdge Mar 25 '18 at 07:14
1

@Andre ... correct.. many thanks .. wrong copy and paste .. answer updated – ScaisEdge Mar 25 '18 at 07:38
sorry I'm a bit confused on the `SELECT` from `my_table1` and `my_table2`....my 2 queries each contain numerous tables via relationships that when combined gets `FieldOne, FieldTwo, FieldThree, CountOne, CountTwo, CountThree`...how can I use this in my `UNION QUERY`?....shouldn't I be referencing my `QueryOne` and `QueryTwo`? – Pangu Mar 25 '18 at 07:40
answer updated .. with a suggestion for you subquery .. hope is clear .. – ScaisEdge Mar 25 '18 at 07:46
I have tried some things, but due to my limited knowledge, I am getting nowhere and producing some weird results in the query....is it possible to talk with you in chat without bombarding here with back-to-back responses? – Pangu Mar 25 '18 at 08:22
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/167499/discussion-between-scaisedge-and-pangu). – ScaisEdge Mar 25 '18 at 08:22

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

1 Answers1

Linked