# how to show sum(cost) and revenue from two tables and display per month[Answered] RSS

## 3 replies

Last post Dec 08, 2011 12:23 PM by vytautas.ziurlis

• kblurry

Member

2 Points

6 Posts

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


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

Contributor

2497 Points

686 Posts

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

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?

• kblurry

Member

2 Points

6 Posts

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

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

• vytautas.ziu...

Contributor

2497 Points

686 Posts

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

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]```