A tricky question... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

A tricky question...

Author

Topic

lsy
Yak Posting Veteran

57 Posts

Posted - 2005-09-21 : 20:30:38

if i got 2 table
Table1
-------
WorkWeek Value
28 212
28 242
28 435
28 534
29 254
29 542
30 984

Table2
------
WorkWeek ValueX
28 453
28 22
29 124
29 232
29 12
30 54
30 546
30 324

i want my result as
WorkWeek
Sum(Table1.Value) AS SumVal
COUNT(Table2.ValueX) AS CountValX
Sum(Table1.Value) / COUNT(Table2.ValueX) AS Answer

WorkWeek SumVal CountValX Answer
---------------------------------------------
28 1423 2 7115
29 706 3 265.33
30 984 3 328

so how can my sql statement should look like... join 2 table which get the sum of Value in Table1 and count the row of table 2 which both group by WorkWeek and take both to devided it...
Is it possible make it in one sql statement?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 02:59:47

Try this


Select T1.WorkWeek, SumVal,CountValX,SumVal/CountValX*1.0 from
	(
	Select WorkWeek, sum(value) as SumVal from table1 group by WorkWeek
	) T1
	inner join 
	(
	Select WorkWeek, count(ValueX) as CountValX from table2 group by WorkWeek
	) T2
on T1.WorkWeek=T2.WorkWeek

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources