PostgreSQL – Proper Usage of GROUP BY in INNER JOIN

group bypostgresql

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

Best Answer

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

Related Solutions

Sql-server – SQL join query to show rows with non-existent rows in one table

Thank you for SQLfiddle and sample data! I wish more questions started this way.

If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL on that side (because it doesn't know if NULL would match the filter or not).

I modified the first query to get a row for every member:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE etc.

Some other notes:

please always use the schema prefix when creating and referencing objects
please always use a length when converting to varchar etc.
stay away from ambiguous, regional date formats like mm-dd-yyyy

consider using aliases to make your queries easier to read. E.g. the above could be re-written as:

SELECT m.Member_ID
  ,t.Date_Start
  ,t.Hours_Actual
  ,t.Hours_Bill
FROM dbo.Members AS m
LEFT OUTER JOIN dbo.Time_Entry AS t
ON m.Member_ID = t.Member_ID
AND t.Date_Start = '20131110';

... a lot tidier, IMHO, as long as you use sensible aliases.

Mysql – GROUP BY on two large joined tables

Sorry, but you have designed yourself into a corner.

Strike 1: EAV (key-value) schema sucks when the tables get large. Further discussion and possible workarounds: http://mysql.rjweb.org/doc.php/eav

Strike 2: GUIDs (and UUIDs, MD5s, etc) suck as KEYs because they are so random. Further discussion and a possible (but unlikely) workaround: http://mysql.rjweb.org/doc.php/uuid

Your best choice may be to keep buying RAM so that everything can be cached. And/or switch from spinning drives to SSDs. Of course, neither hardware solution scales well.

Software solution involves a major rework of the schema and application code. That is beyond the scope of this forum, other than to point you at those two blogs.

As for your specific questions:

On the explain I always get a “Using where; Using index; Using temporary; Using filesort” on the Person table. Why does MySql need to sort the Person table before joining to PersonData?

What you are seeing is bogus. EXPLAIN likes to put those flags on the first line, even though they might apply to some other line. If you have a new enough version of MySQL, do EXPLAIN FORMAT=JSON SELECT ...; it should spell out things correctly.

Why does MySql generally decline to use the PersonID/FieldID/Value index? Is there a better index I could use? I assumed I needed the PersonID in the index to help with the join.

I need to see SHOW CREATE TABLE. But here is a guess... If the WHERE clause matches more than ~20%, it will choose to do a table scan because it is likely to be faster. Another guess: You have PRIMARY KEY(person, field) and INDEX(person, field, value)?? And you are using InnoDB?? The PK includes all columns and is sorted by (person, field). That's identical to the secondary key! So, the optimizer may as well flip a coin to decide between them.

Anyway, it says "Using index"; that about as efficient as it gets.

Best Answer

Related Solutions

Sql-server – SQL join query to show rows with non-existent rows in one table

Mysql – GROUP BY on two large joined tables

Related Question