skip to Main Content

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


  1. 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. 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. 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