PostgreSQL How to get average of sum of certain rows

Question

I apologize if the title isn't very apt, but I had a little trouble formulating a concise title. Anyway, I have a table that keeps a log of states a person is in. It looks like:

id, login, state, duration, started_at
1, pdiddy, working, 1200, 2018-05-25 08:30:00
2, pdiddy, lunch, 120, 2018-05-25 9:00:00
3, pdiddy, on_call, 65, 2018-05-25 12:30:00
4, pdiddy, available, 1115, 2018-05-25 12:30:00
5, pdiddy, working, 143, 2018-05-25 12:30:00
6, pdiddy, break1, 150, 2018-05-25 12:30:00
7, pdiddy, working, 2400, 2018-05-25 12:30:00
8, pdiddy, break2, 110, 2018-05-25 12:30:00

I need to get an average for only labor-related durations on a day-by-day basis for each user. So basically I need to add up the durations for everything other than "lunch", "break1", and "break2" for any given day and get the average for it.

I tried to do this like this, but the problem with this is that it doesn't add up the labor-related logs before averaging. I can't figure out how to do that.

SELECT
    log.login,
    AVG(log.duration) FILTER (WHERE log.state NOT IN ('lunch', 'break1', 'break2')) AS "labor_average"
FROM
    log
GROUP BY 1

Obviously I don't expect anybody to just do this for me. I just need to be pointed in the right direction. I'm obviously quite a ways from the solution, so I just need a push in the right direction. Thank you very much in advance!

average of the sum of the durations which are not "lunch", "break1" or "break2" — Luke Visinoni
– Luke Visinoni, Commented May 30, 2018 at 14:55
sorry about that. I fixed my description. should be a little less nonsensical now — Luke Visinoni
– Luke Visinoni, Commented May 30, 2018 at 14:58

Gordon Linoff · Accepted Answer · 2018-05-30 15:08:05Z

1

I think you want the total sum divided by the number of days:

SELECT l.login,
       (SUM(l.duration) FILTER (WHERE l.state NOT IN ('lunch', 'break1', 'break2')) /
        COUNT(DISTINCT date_trunc('day', l.started_at)
       ) AS labor_average
FROM log l
GROUP BY l.login

answered May 30, 2018 at 15:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vladimir Baranov Over a year ago

Should the COUNT have the same FILTER as SUM? Why use FILTER at all? Why not simple WHERE?

Luke Visinoni Over a year ago

this result came out almost exactly to what the other response did. I think this is the solution I have to go with though because I don't think I can do subqueries with the tools I'm using unfortunately. Thank you!

Gordon Linoff Over a year ago

@VladimirBaranov . . . I am guessing there are "work" states on all days with a break. If there are not, it is not clear whether or not the OP wants to count them.

Vladimir Baranov · Accepted Answer · 2018-05-30 15:04:09Z

0

Calculate sum per login and per day first.

Then calculate average per login.

SELECT
    login, AVG(SumDuration) AS AvgDuration
FROM
    (
    SELECT
        login, started_at::date, SUM(duration) AS SumDuration
    FROM log
    WHERE log.state NOT IN ('lunch', 'break1', 'break2')
    GROUP BY login, started_at::date
    ) AS T
GROUP BY login;

answered May 30, 2018 at 15:04

Vladimir Baranov

32.9k5 gold badges60 silver badges95 bronze badges

1 Comment

Luke Visinoni Over a year ago

Sorry I'm having server timeouts for some reason. Trying to test your answers I will get back to you. Thanks for the responses so far.

Collectives™ on Stack Overflow

PostgreSQL How to get average of sum of certain rows

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related