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