Not getting expected results

Transact-SQL
1

Here is the query:

SELECT       l1.id AS link_id,
             l1.link,
			 l1.title,
			 l1.time_submitted,
			 (sum(i1.leaning_code)) AS interesting_weight,
			 (sum(p1.leaning_code)) AS political_weight

FROM         tbl_link l1
LEFT OUTER
JOIN		 tbl_leaning_interesting i1
ON			 l1.id = i1.link_id
LEFT OUTER
JOIN		 tbl_leaning_political p1
ON			 l1.id = p1.link_id
GROUP BY	 l1.id, l1.link, l1.title, l1.time_submitted
order by     l1.time_submitted desc

tbl_leaning_interesting has 4 entries: 1 '-1' and 3 '1'. This should add up to 2 for the sum column, but I am getting 8. Any idea why this is? Thank you for any help.

2

See what is the output of query without the SUM and GROUP BY.

3

You probably need to do the aggregates first:

WITH LInteresting
AS
(
    SELECT link_id, SUM(leaning_code) AS interesting_weight
    FROM tbl_leaning_interesting
    GROUP BY link_id
)
,LPolitical
AS
(
    SELECT link_id, SUM(leaning_code) AS political_weight
    FROM tbl_leaning_political
    GROUP BY link_id
)
SELECT L.id AS link_id, L.link, L.title, I.interesting_weight, P.political_weight
FROM tbl_link L
    LEFT JOIN LInteresting I ON L.id = I.link_id
    LEFT JOIN LPolitical P ON L.id = P.link_id
ORDER BY time_submitted DESC;