Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

MySQL combine results of two unrelated tables

I’m trying to merge two query results in to one:
Query 1 and the reulsts:

    SELECT MONTHNAME(tblFeesPaid.Pay_Date) AS 'Month', 
            SUM(tblFeesPaid.Fees_Paid) As 'Total Fees' 
    FROM tblFeesPaid 
        INNER JOIN tblFeesStructure ON tblFeesPaid.FID=tblFeesStructure.ID 
    WHERE Year(tblFeesPaid.Pay_Date)=2022 
    GROUP BY month(tblFeesPaid.Pay_Date);

Results

    Month    Total Fees 
    January  162000.00
    February 69000.00
    March    146926.00

Query 2 and results

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

    SELECT MONTHNAME(tblTransFeesPaid.Pay_Date) AS 'Month', 
            SUM(tblTransFeesPaid.TransFee_Paid) As 'Transport Fees' 
    FROM tblTransFeesPaid 
        INNER JOIN tbltransfeesstructure ON tblTransFeesPaid.TransFID=tbltransfeesstructure.ID 
    WHERE Year(tblTransFeesPaid.Pay_Date)=2022 
    GROUP BY month(tblTransFeesPaid.Pay_Date);

Results

    Month Transport Fees
    March 7000.00

Could someone help me with the correct syntax that I’ll achieve results as follows:

Expected results:

 Month    Total Fees  Transport Fees
 January  162000.00
 February 69000.00
 March    146926.00   7000.00

>Solution :

SELECT Z.Month, sum(Z.TotalFees) As 'Total Fees', sum(Z.TransportFees) As 'Transport Fees'
FROM
(
SELECT MONTHNAME(tblFeesPaid.Pay_Date) AS 'Month',
       SUM(tblFeesPaid.Fees_Paid) As 'TotalFees',
       0 As 'TransportFees'
FROM tblFeesPaid 
INNER JOIN tblFeesStructure ON tblFeesPaid.FID=tblFeesStructure.ID 
WHERE Year(tblFeesPaid.Pay_Date)=2022 
GROUP BY month(tblFeesPaid.Pay_Date)
UNION
SELECT MONTHNAME(tblTransFeesPaid.Pay_Date) AS 'Month',
       0 As 'TotalFees',
       SUM(tblTransFeesPaid.TransFee_Paid) As 'TransportFees' 
FROM tblTransFeesPaid 
        INNER JOIN tbltransfeesstructure ON tblTransFeesPaid.TransFID=tbltransfeesstructure.ID 
    WHERE Year(tblTransFeesPaid.Pay_Date)=2022 
    GROUP BY month(tblTransFeesPaid.Pay_Date)) Z
GROUP BY Z.Month;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading