Filter a grouped by result in PostgresSQL

antipopp 0
Viewed 49

I have a planning table like this:

ID EmployeeID ExternalID JobID
abc 1 null a
def 1 null b
ghi null 2 a
jkl null 2 f
mno 5 null d

I want to filter the rows by using an AND condition between EmployeeID and ExternalID grouping by the JobID. If I want the plannings for Employee 1 and External 2 the result should be:

ID EmployeeID ExternalID JobID
abc 1 null a
ghi null 2 a

I'm trying using subqueries but the group by is making it hard to then filter the result with a simple WHERE EmployeeID = 1 AND ExternalID = 2.

Since the result should be displayed on a frontend table, where the filtering happens, I want it to return all the JobIDs that have the selected Employees and Externals. The JobID is not given beforehand.

sql postgresql
2 Answers

We can try to use the condition aggregate function with your condition in a subquery and then filter the count whether greater than 0.

SELECT ID,EmployeeID,ExternalID,JobID
FROM (
    SELECT *, 
           COUNT(CASE WHEN EmployeeID = 1 THEN 1 END) OVER(PARTITION BY JobID) cntEmployeeID,
           COUNT(CASE WHEN ExternalID = 2 THEN 1 END) OVER(PARTITION BY JobID) cntExternalID
    FROM T
) t1
WHERE cntEmployeeID > 0 AND cntExternalID > 0

sqlfiddle

or PostgreSQL support filter that we can try to use

SELECT ID,EmployeeID,ExternalID,JobID
FROM (
    SELECT *, 
           COUNT(*) FILTER (WHERE EmployeeID = 1) OVER(PARTITION BY JobID) cntEmployeeID,
           COUNT(*) FILTER (WHERE ExternalID = 2) OVER(PARTITION BY JobID) cntExternalID
    FROM T
) t1
WHERE cntEmployeeID > 0 AND cntExternalID > 0
D-Shih0

Found a solution to my problem with this query:

SELECT *
FROM
    (
        SELECT
            JobID,
            array_agg(EmployeeID) AS employee,
            array_agg(ExternalID) AS external
        FROM
            plannings
        GROUP BY
            JobID
    ) t1
WHERE
    array ['1', '5'] <@ employee -- using array so I can look up multiple IDs
    AND array ['2'] <@ external

I had to convert the JobIDs to string in order to use the <@ operator.

https://dbfiddle.uk/KPpiPgmo

antipopp0
Related