How to get sum of amount from multiple tables?

I have three (3) tables which l want to get the sum total of amount from it.

OrderDetail

LearnerDetail

StoreDetail

This is what I've tried.

SELECT 
    DATEPART(m, orderdate) AS Month,
    Total = (SELECT SUM(amount)
             FROM orderdetail) +
            (SELECT SUM(amount)
             FROM learnerdetail) +  
            (SELECT SUM(amount)
             FROM storedetail)
GROUP BY 
    DATEPART(m, orderdate) 

Expected output

One possible solution involves leveraging the WITH clause to create a temporary table that unifies the desired data from various sources (Common Table Expression). Subsequently, we employ the SELECT statement with the GROUP BY clause to obtain the desired aggregate values.

WITH mytable
     AS (SELECT amount,
                order_date
         FROM   order_details
         UNION ALL
         SELECT amount,
                order_date
         FROM   learner_details
         UNION ALL
         SELECT amount,
                order_date
         FROM   store_details)
SELECT Sum(amount),
       Month(order_date)
FROM   mytable
GROUP  BY Month(order_date); 

Here the SQL query combines data from three tables (order_details, learner_details, store_details) into a common table. It then calculates the sum of the "amount" column for each month, grouping the results accordingly.

Here you can use GROUP BY EOMONTH(order_date) which is probably better, especially if you have more than 12 months of data.