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
Query
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 
Desired output
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
Current output
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.

Answers


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


Need Your Help

Confused about C++'s std::wstring, UTF-16, UTF-8 and displaying strings in a windows GUI

c++ unicode utf-8 utf-16 wstring

I'm working on a english only C++ program for Windows where we were told "always use std::wstring", but it seems like nobody on the team really has much of an understanding beyond that.

MongoDB and recursion in Collections

mongodb infinite

Reading the introduction to MongoDB I am a little confused. It seems the Database is made of Collections, which are made of documents, which are made of fields, which are made of key/value pairs. The