How to support two conditions in the group by clause (postgresql)

Question

I have three table TableA, TableB, TableC they all are linked by id. Table A has logTime and tableB has detectionTime and TableC has emptId.

For each empId I want 1. count of logTime when no detectionTime found in tableB 2. average of logTime and detectionTime difference if detectionTime found.

I have written this query that gets the average when detectionTime found but not sure how to get data when detectionTime not found

select det.empid  ,average(det.blocked_time) 
FROM (SELECT c.emplid as empid, EXTRACT(EPOCH FROM ( a.detectionTime - b.logTime )) as blocked_time
        ## inner and join condition here
  ) As det group by det.emplid where dat.blocked_time>0

Here I got entry when blocked_time>0 average and other stats.

How to get count of blocked_time for each empId when blocked_time is 0

Kindly share sample data and desired output..

zarruq
– zarruq

2017-11-29 04:12:22 +00:00
Commented Nov 29, 2017 at 4:12 — zarruq
– zarruq, Commented Nov 29, 2017 at 4:12

Laurenz Albe · Accepted Answer · 2017-11-29 04:26:00Z

2

First, a few comments:

Your. SQL statement is syntactically incorrect. WHERE must come before GROUP BY.
Your query seems to assume that id is a unique key for all three tables. Otherwise the same value could appear in the join result multiple times.

The expression you are looking for is

count(blocked_time) FILTER (WHERE blocked_time = 0)

answered Nov 29, 2017 at 4:26

Laurenz Albe

257k22 gold badges311 silver badges388 bronze badges

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

Comments

zarruq · Accepted Answer · 2017-11-29 04:24:45Z

1

As no sample data is shared, so an (assumed) option could be to use left join and coalesce in inner query to convert NULL values for detectionTime to 0 and then use conditional aggregation in outer query as below.

select det.empid  ,
       avg(case when detectionTime  > 0 then det.blocked_time end) as avgBlockedTime, 
       sum(case when detectionTime =  0 then 0 else 1 end) as logTimeCount
FROM (SELECT c.emplid as empid, 
      EXTRACT(EPOCH FROM ( a.detectionTime - b.logTime )) as blocked_time,
      coalesce(DetectionTime,0) as detectionTime
      ## left outer join and condition here
  ) As det 
group by det.empid

answered Nov 29, 2017 at 4:24

zarruq

2,4652 gold badges12 silver badges20 bronze badges

2 Comments

Alchemist Over a year ago

Thanks so much @zarruq for helping me solve this issue. I want to know how to add this case statement to calculate median. Currently i am calling median using TO_CHAR(((SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER by det.blocked_time)) || ' second')::interval, 'HH24:MI:SS') MedianBlockedTime,

zarruq Over a year ago

@Alchemist: Kindly ask another question. Someone will help you for sure with that also :-)

G.Arima · Accepted Answer · 2017-11-29 04:57:25Z

1

Try this out:

select emptid,
sum(case when detectiontime is null then 1 else 0 end) as count,
average(case when detectiontime is not null then diff else 0 end) as avg
(
select a.*,b.detectiontime,c.emptid,a.logtime-b.detectiontime as diff
from
tablea a
left join
tableb b
on a.id = b.id
left join
tablec c
on a.id = c.id
)
group by emptid;

Let me know if you are looking for something else.

answered Nov 29, 2017 at 4:57

G.Arima

1,1711 gold badge6 silver badges13 bronze badges

1 Comment

Alchemist Over a year ago

Thanks G.Arima. I just want to know to add this case condition with median. Median function that I am using is PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER by det.blocked_time)

Collectives™ on Stack Overflow

How to support two conditions in the group by clause (postgresql)

3 Answers 3

Comments

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Related