Combine same columns from multiple tables-CodePudding

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!

CodePudding user response：

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:

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

CodePudding user response：

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

CodePudding user response：

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;