Question 113046 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Combine same columns from multiple tables

I have two tables that have the same columns; AgencyA & AgencyB. Columns; Subject, Event_Combined and License_Fee. How can I combine the columns for each table? IE Subject column will have all the data from AgencyA and AgencyB.
Ive tried this

SELECT Subject, Event_Combined, License_Fee
FROM AgencyA
UNION ALL
SELECT Subject, Event_Combined, License_Fee
FROM AgencyB

Which combines everything but how do I run the query below?

SELECT 
    Subject,
    SUM(License_Fee) as Gross,
    COUNT(DISTINCT(Event_Combined)) as Total_Sales,
    SUM(License_Fee)/COUNT(DISTINCT(Event_Combined)) as Result
    FROM AgencyA 
    GROUP BY Subject
    ORDER BY Gross DESC

Thanks!

Tags:

3

Answers


  1. 0

    If what you seek is to have the sql (from your question), work with both AgencyA and AgencyB in same result, you can do this with for example UNION ALL, as you mentioned. Since a result only can be sorted once, you just need to only have "ORDER BY" at the end of the query.

    SELECT 
    Subject,
    SUM(License_Fee) as Gross,
    COUNT(DISTINCT(Event_Combined)) as Total_Sales,
    SUM(License_Fee)/COUNT(DISTINCT(Event_Combined)) as Result
FROM AgencyA 
    GROUP BY Subject
UNION ALL 
SELECT 
    Subject,
    SUM(License_Fee) as Gross,
    COUNT(DISTINCT(Event_Combined)) as Total_Sales,
    SUM(License_Fee)/COUNT(DISTINCT(Event_Combined)) as Result
FROM AgencyB
    GROUP BY Subject
    ORDER BY Gross DESC

    Without knowing how the data in those tables looked like, which could have helped, I tried to replicate and got following result:
    example result of code

    For very basic understanding on UNION, where also the ORDER rule is mentioned, please look at guides online as for example MySQL UNION Operator

    Login or Signup to reply.
  2. 0

    You can create a derived table and work with all data as with one table

    Example:

    SELECT City, Count(*) as count FROM
(SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City) as tmp GROUP BY city;

    In your case it would be something like:

    SELECT 
Subject,
SUM(License_Fee) as Gross,
COUNT(DISTINCT(Event_Combined)) as Total_Sales,
SUM(License_Fee)/COUNT(DISTINCT(Event_Combined)) as Result
FROM 
(SELECT Subject, Event_Combined, License_Fee
FROM AgencyA
UNION ALL
SELECT Subject, Event_Combined, License_Fee
FROM AgencyB) as tmp
GROUP BY Subject
ORDER BY Gross DESC

    Btw, you may use a view for this (to optimize the query):
    https://blog.devart.com/how-to-create-a-view-in-mysql.html

    Login or Signup to reply.
  3. 0

    Or you could combine your two queries into one the lazy way:

    WITH Agencies AS (
SELECT Subject, Event_Combined, License_Fee
FROM AgencyA
UNION ALL
SELECT Subject, Event_Combined, License_Fee
FROM AgencyB
)
SELECT 
    Subject,
    SUM(License_Fee) as Gross,
    COUNT(DISTINCT(Event_Combined)) as Total_Sales,
    SUM(License_Fee)/COUNT(DISTINCT(Event_Combined)) as Result
    FROM Agencies 
    GROUP BY Subject
    ORDER BY Gross DESC;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search