Question 114789 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Filter a grouped by result in PostgresSQL

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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


  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search