Postgres where clause over two columns from subquery

• Database: Postgres
• Table name: records
• Has 4 columns Year | Dept | Expense | Month

So per year there can be up to one record for each month / per department.

I have a sub-query which returns me the below data based on constraints.

Year | Dept
2001 | HR
2001 | ACC
2001 | TECH
2002 | TECH
2003 | HOUSE-KEEPING
2003 | TECH

Now I need to run another query on the same table to get a total of the expenses for each value pair.

Using the output of the subquery, my main query will be something like

select Dept, sum(Expense) 
from records 
where (Year, Dept) in (...)
... I want to iterate over value pairs from the subquery

(SUBQUERY which returns
Year | Dept
2001 | HR
2001 | ACC
2002 | TECH
2003 | HOUSE-KEEPING
)

When I run this select statement, the output returned is only containing data for TECH across all three years.

Please can help understand how I can run the main select over all the rows of the subquery

Answer

Refactor the subquery to a join.

Say you have

SELECT a, b FROM t1 WHERE (x,y) IN (SELECT x1, y1 FROM t2 WHERE ...)

which won’t work. You rewrite to

SELECT a, b
FROM t1
INNER JOIN (
   -- subquery here:
   SELECT x1, y1
   FROM t2
   WHERE ...
) AS some_alias
WHERE t1.x = some_alias.x1
  AND t1.y = some_alias.y1;

Note, in PostgreSQL you shouldn’t use a CTE (WITH query) for this.

You can abbreviate

WHERE t1.x = some_alias.x1
  AND t1.y = some_alias.y1;

to

WHERE (x, y) = (x1, y1)

though.