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!
3
Answers
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.
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
You can create a derived table and work with all data as with one table
Example:
In your case it would be something like:
Btw, you may use a view for this (to optimize the query):
https://blog.devart.com/how-to-create-a-view-in-mysql.html
Or you could combine your two queries into one the lazy way: