Postgres where clause over two columns from subquery

Question

• 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

Solution 1

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.

Solution 2

If you haven't got millions of rows, you can keep the semantic of your query by concatenating the values into one value:

select Dept, sum(Expense)
from records
where Year || Dept in (select Year || Dept from ...)

It's easy to read, understand and maintain, and it will perform just fine unless you have zillions of years and departments, which seems highly unlikely.

Solution 3

Solution found here:

SELECT dept, SUM(expense) FROM records 
WHERE ROW(year, dept) IN (SELECT x, y FROM otherTable)
GROUP BY dept;

The ROW function does the magic.