sql-server-2014

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

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

Solution

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