how to show sum(cost) and revenue from two tables and display per month

• Question

• User-1098969383 posted
 i have two tables: ``table 1:Expense costExpense datetable 2:Employee costRvenueEmployee dateTotalcost = Expense cost + Employee costPofit = Revenue - TotalCost`` i have to display the report like so. It should show data per month according to year lets say 2011, (i know how to filter by year) ``   Totalcost | Revenue | Profitjanfebmaraprmay junjulaugsepoctnovdec`` I'm having a tough time fugring out the query. The problem is how can i display the data in report viewer table
Thursday, December 8, 2011 9:30 AM

• User992646781 posted

The following should do the trick:

```SELECT [Month], SUM(Cost) AS TotalCost, SUM(Revenue) AS Revenue, SUM(Revenue - Cost) AS Profit
FROM (
SELECT MONTH([Date]) AS [Month], SUM(Cost) AS Cost, 0 AS Revenue
FROM Table1
GROUP BY MONTH([Date])
UNION ALL
SELECT MONTH([Date]), 0, SUM(Cost), SUM(Revenue)
FROM Table2
GROUP BY MONTH([Date])
) tmp
GROUP BY [Month]
ORDER BY [Month]```
• Marked as answer by Thursday, October 7, 2021 12:00 AM
Thursday, December 8, 2011 12:23 PM

All replies

• User992646781 posted

Table 1 and Table 2 you described do not have any dates in them. How is grouping supposed to work without dates? Also, is there a relationship between employee and expense?

Thursday, December 8, 2011 9:56 AM
• User-1098969383 posted

sorry, i upated my post. yes there is a difference betwen expense cost and employee cost

Thursday, December 8, 2011 11:54 AM
• User992646781 posted

The following should do the trick:

```SELECT [Month], SUM(Cost) AS TotalCost, SUM(Revenue) AS Revenue, SUM(Revenue - Cost) AS Profit
FROM (
SELECT MONTH([Date]) AS [Month], SUM(Cost) AS Cost, 0 AS Revenue
FROM Table1
GROUP BY MONTH([Date])
UNION ALL
SELECT MONTH([Date]), 0, SUM(Cost), SUM(Revenue)
FROM Table2
GROUP BY MONTH([Date])
) tmp
GROUP BY [Month]
ORDER BY [Month]```
• Marked as answer by Thursday, October 7, 2021 12:00 AM
Thursday, December 8, 2011 12:23 PM