2

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

2 Answers 2

0

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
10
  • Thanks for the help Sir, but can you SUM(loginhours) with date specified? like 2015-07-17. I need the login hours to be sum up for each user on the date specified like 2015-07-17 Jul 17, 2015 at 13:47
  • Yes, if you are looking for results for each agent per date, then you can do group by agent_id, created_at and remove it from the filter criteria Jul 17, 2015 at 13:58
  • Sorry I'm new to SQL, how should that look like in your given query? Jul 17, 2015 at 14:02
  • I just modified the answer. Check that Jul 17, 2015 at 14:07
  • Sir, ERROR: syntax error at or near "(" LINE 3: A.created_at SUM(TotalLoginHours) AS TotalLoginHours... Jul 17, 2015 at 14:11
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

3
  • Hi thanks for helping. I like your query it's much shorter, I also made a query which returns same result as yours but much longer and I dunno if it is a good practice, take a look at sqlfiddle.com/#!15/8d88b/10 Then tell if it is ok or not then why? If not I'll use yours and accepts it as answer. thanks Jul 17, 2015 at 13:35
  • updated it sqlfiddle.com/#!15/8d88b/11 Jul 17, 2015 at 13:48
  • I see someone else answered your question so I'll just comment on your query in the fiddle. The basic problem with your query is that you are repeating the same calculation over and over again. A sufficiently smart optimizer could in theory detect this and rewrite it for you, but I don't think we are there just yet. Common Table Expressions (CTE) might come in handy in such situations, I suggest you have a look at that in the documentation Jul 17, 2015 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.