SQL join double counting
I've found somewhat similar posts, but can't figure out how to apply in my case. Essentially in joining two tables I'm getting double counting in the output. Here's what I'm looking at:
Table 1 (t1)
Name | Type | Count Chris Blue 3 Chris Red 2 Steve Blue 10 Steve Red 5 Steve Green 4 Peter Blue 7
Table 2 (t2)
Name | Hours Chris 2.4 Steve 1.5 Peter 0.2
SELECT t1.name, t1.type, t1.count, t2.hours / count(t1.name) as avg_hrs FROM t1 JOIN t2 on t1.name = t2.name GROUP BY t1.name, t1.type
Name | Type | Count | Hours Chris Blue 3 1.2 Chris Red 2 1.2 Steve Blue 10 0.5 Steve Red 5 0.5 Steve Green 4 0.5 Peter Blue 7 0.2
Name | Type | Count | Hours Chris Blue 3 2.4 Chris Red 2 2.4 Steve Blue 10 1.5 Steve Red 5 1.5 Steve Green 4 1.5 Peter Blue 7 0.2
The 'Hours' column is coming out wrong. Basically, I want to divide the hours in t2 by the number of occurrences of the name in t1; instead I am getting the full value of t2.hours for each row in the newly produced table, which is leading to a bunch of double/triple counting when I look at the aggregate table.
Any ideas on how to fix? Thanks for any pointers.
You can do as:
select t1.name, t1.type, t1.count, t2.hours/t1count.total as hours from t1 inner join t2 on (t1.name = t2.name) inner join (select name, count(*) total from t1 group by name) t1count on (t1.name = t1count.name)
See it here on fiddle: http://sqlfiddle.com/#!9/27a07a/4