How to get sum of amount from multiple tables?

Question

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

OrderDetail

Product	Amount	OrderDate
Apple	10	2023-09-01
Orange	5	2023-10-01

LearnerDetail

Name	Amount	OrderDate
Tutor	30	2023-09-01
Levy	10	2023-09-01

StoreDetail

Name	Amount	OrderDate
Loc A	5	2023-09-01
Loc B	5	2023-10-01

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

OrderDate	Total
2023-09-01	55
2023-10-01	10

Answer 1

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.