Join Multiple Select Queries into One in MySql

September 22, 2023

I will be perfectly honest and admit that I do not have a full understanding of MySql. But I have managed to help myself so far. The problem I am having is that I need to "merge" five queries into one, but am unable to do so. Below are the queries:

select month(AssDate) as mon, Round(AVG(DATEDIFF(AssDate,OpenedDate))) as RxInsp from tbl_jobcards where year(OpenedDate) = 2023 and month(AssDate) > 0 group by mon order by mon; 
select month(QuoteSent) as mon, Round(AVG(DATEDIFF(QuoteSent, AssDate))) as InspQs from tbl_jobcards where year(OpenedDate) = 2023 and month(QuoteSent) > 0 group by mon order by mon; 
select month(OrderReceived) as mon, Round(AVG(DATEDIFF(OrderReceived, QuoteSent))) as QsQx from tbl_jobcards where year(OpenedDate) = 2023 and month(OrderReceived) > 0 group by mon order by mon; 
select month(Repair1Date) as mon, Round(AVG(DATEDIFF(Repair1Date,QuoteSent))) as QtRp from tbl_jobcards where year(OpenedDate) = 2023 and month(Repair1Date) > 0 group by mon order by mon; 
select month(DateDelivered) as mon, Round(AVG(DATEDIFF(DateDelivered,Repair1Date))) as RpDl from tbl_jobcards where year(OpenedDate) = 2023 and month(DateDelivered) > 0 group by mon order by mon;

EDIT:
Here is some sample data.

The results I am getting from these queries are correct. I just need it in one table and just can’t seem to figure out how.

I tried using Join, Union, and Views. But none of those methods returned the desired result. I also tried nested select statements to no avail.

>Solution :

In the first step, you could stack your result sets vertically into a combined table using something like this:

select month(AssDate) as mon, 'RxInsp' AS TheTitle, Round(AVG(DATEDIFF(AssDate,OpenedDate))) as TheValue
from tbl_jobcards
where year(OpenedDate) = 2023 and month(AssDate) > 0
group by mon

UNION ALL 

select month(QuoteSent) as mon, 'InspQs' AS TheTitle, Round(AVG(DATEDIFF(QuoteSent, AssDate))) as TheValue
from tbl_jobcards
where year(OpenedDate) = 2023 and month(QuoteSent) > 0
group by mon

UNION ALL 

select month(OrderReceived) as mon, 'QsQx' AS TheTitle, Round(AVG(DATEDIFF(OrderReceived, QuoteSent))) as TheValue
from tbl_jobcards
where year(OpenedDate) = 2023 and month(OrderReceived) > 0
group by mon

UNION ALL 

select month(Repair1Date) as mon, 'QtRp' AS TheTitle, Round(AVG(DATEDIFF(Repair1Date,QuoteSent))) as TheValue
from tbl_jobcards
where year(OpenedDate) = 2023 and month(Repair1Date) > 0
group by mon

UNION ALL 

select month(DateDelivered) as mon, 'RpDl', AS TheTitle, Round(AVG(DATEDIFF(DateDelivered,Repair1Date))) as TheValue
from tbl_jobcards
where year(OpenedDate) = 2023 and month(DateDelivered) > 0
group by mon

Then in order to generate this with one row per month, and a column for each of the values, you could use:

SELECT TheTable.mon,
    SUM(IF(TheTitle = 'RxInsp', TheValue, NULL)) AS RxInsp,
    SUM(IF(TheTitle = 'InspQs', TheValue, NULL)) AS InspQs,
    SUM(IF(TheTitle = 'QsQx', TheValue, NULL)) AS QsQx,
    SUM(IF(TheTitle = 'QtRp', TheValue, NULL)) AS QtRp,
    SUM(IF(TheTitle = 'RpDl', TheValue, NULL)) AS RpDl
FROM (

    select month(AssDate) as mon, 'RxInsp' AS TheTitle, Round(AVG(DATEDIFF(AssDate,OpenedDate))) as TheValue
    from tbl_jobcards
    where year(OpenedDate) = 2023 and month(AssDate) > 0
    group by mon

    UNION ALL 

    select month(QuoteSent) as mon, 'InspQs' AS TheTitle, Round(AVG(DATEDIFF(QuoteSent, AssDate))) as TheValue
    from tbl_jobcards
    where year(OpenedDate) = 2023 and month(QuoteSent) > 0
    group by mon

    UNION ALL 

    select month(OrderReceived) as mon, 'QsQx' AS TheTitle, Round(AVG(DATEDIFF(OrderReceived, QuoteSent))) as TheValue
    from tbl_jobcards
    where year(OpenedDate) = 2023 and month(OrderReceived) > 0
    group by mon

    UNION ALL 

    select month(Repair1Date) as mon, 'QtRp' AS TheTitle, Round(AVG(DATEDIFF(Repair1Date,QuoteSent))) as TheValue
    from tbl_jobcards
    where year(OpenedDate) = 2023 and month(Repair1Date) > 0
    group by mon

    UNION ALL 

    select month(DateDelivered) as mon, 'RpDl', AS TheTitle, Round(AVG(DATEDIFF(DateDelivered,Repair1Date))) as TheValue
    from tbl_jobcards
    where year(OpenedDate) = 2023 and month(DateDelivered) > 0
    group by mon

) TheTable

GROUP BY TheTable.mon
ORDER BY TheTable.mon