Proper usage of Group by in Inner Join

Question

EDIT I updated my question with a SQL Fiddle Sample http://sqlfiddle.com/#!15/8d88b/1

I'm currently making a report from a database records but I don't know how my query should look like, first of all I have 2 tables. Application Forms, and a table for Login Hours of each user

forms
->id
->agent_id
->SomeInfo
->created_at


loginhours
->id
->user_id
->loginhours (decimal)
->created_at

And I have report with the following columns

UserID, TotalLoginHours, TotalApplication, Application Per Hour (aph), Revenue Per Hour (rph)

So right now I have this query

SELECT a.agent_id, SUM(b.loginhours) as TotalLoginHours, COUNT(a.id) as TotalApplication, SUM(b.loginhours) / COUNT(a.id) as ApplicationPerHour,   (SUM(b.loginhours) / COUNT(a.id)) * 1.75 as RPH 
FROM forms a 
INNER JOIN loginhours b ON a.agent_id = b.user_id WHERE a.created_at = '2015-07-17' 
GROUP BY a.agent_id

Note that user_id and agent_id is the same.

I want to get the result based on the date selected, example 2015-07-17 I got results but my problem is the loginhours is being SUM based on the number of application for each user. So for example the user1 has 2 records on forms table and his loginhours from 2015-07-17 is 2 then in my result the loginhours becomes 4 which is wrong, I think it is on my GROUP BY statement. Can you help me how to properly query this? Thanks

Answer 1

Query for per agent per day with user name.

Re-frame the Query as per your user table.

    SELECT
        A.agent_id,
        users.NAME,
        A.created_at,
        SUM (TotalLoginHours) AS TotalLoginHours,
        COUNT (A.ID) AS TotalApplication,
        SUM (TotalLoginHours) / COUNT (A.ID) AS ApplicationPerHour,
        (
            SUM (TotalLoginHours) / COUNT (A.ID)
        ) * 1.75 AS RPH
    FROM
        (
            SELECT
                A.agent_id,
                A.ID,
                A.created_at,
                SUM (b.loginhours) AS TotalLoginHours
            FROM
                forms A
            INNER JOIN loginhours b ON A.agent_id = b.user_id
            WHERE
                A.created_at >= '2015-07-10'
            GROUP BY
                A.agent_id,
                A.ID,
                A.created_at
        ) A
    INNER JOIN users ON users.user_id = A.agent_id
    GROUP BY
        A.agent_id,
        users.user_id,
        A.created_at

Answer 2

To avoid the multiplication effect you are experiencing, you can do the aggregation first, and then join:

select a.agent_id, a.TotalApplication, b.TotalLoginHours
     , b.TotalLoginHours / a.TotalApplication as ApplicationPerHour
     , 1.75 * b.TotalLoginHours / a.TotalApplication as RPH
from (
    SELECT a.agent_id as agent_id
         , COUNT(a.id) as TotalApplication
    FROM forms a
    WHERE a.created_at = '2015-07-17' 
    GROUP BY a.agent_id
 ) as a
 join (
     select b.user_id as agent_id
          , SUM(b.loginhours) as TotalLoginHours
     from loginhours b 
     group by b.user_id
 ) as b 
     ON a.agent_id = b.agent_id
 ;  

Not sure I got all the details right, but it should give you a hint of a technique that can be used. I modified your fiddle in: http://sqlfiddle.com/#!15/8d88b/9