SUM function problem with inner join

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

SQL Server 2008 Forums

Transact-SQL (2008)

SUM function problem with inner join

Author

Topic

partap26
Starting Member

27 Posts

Posted - 2013-06-06 : 07:58:38

SELECT sd.site_id,
avg(av.dc_volt) as dc_volt, avg(av.curr_temp) as curr_temp,
SUM(at.eb_time) as eb_time, SUM(at.bb_time) as bb_time, SUM(dg_time) as dg_time
FROM site_details sd
INNER JOIN avg_values av ON av.site_id = sd.site_id
INNER JOIN alarm_count ac ON ac.site_id = sd.site_id
INNER JOIN alarm_time at ON at.site_id = sd.site_id
WHERE sd.site_id='S001'
GROUP BY sd.site_id, sd.site_name;

The output is
S001, 43.00, 21.00, 145800, 44220, 141516

But the actual SUM of eb_time value for S001 is 12150. I think so tha problem is with INNER JOIN and when i remove the join the value start to decrese and when there is only on join then i found the actual value.........

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-06 : 08:01:07

Look up LEFT JOIN in Books online..... or Outer Join

Duane.