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

score 16 · Accepted Answer · answered Sep 25 '17 at 14:04

16

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.

answered Sep 25 '17 at 14:04

Craig Ringer

307,061
76
688
778

1

Thanks a lot for the reply Craig. This will get me started. I should have provided more details in the initial question. My subquery is formed using a with query. I had simplified the actual database in hope of simplifying the answering part. Your answer has made me realize it wasn't a great idea. I am going to post a new question with all the details. Thanks a lot for your help again. – Vik G Sep 26 '17 at 06:06
Hi Craig - I have re-posted the question with all the details here - https://stackoverflow.com/questions/46425141/postgres-where-clause-over-two-columns – Vik G Sep 26 '17 at 11:36
3

Doesn't the `inner join` need an `on`? – user3871 May 15 '18 at 02:00
4

@Growler Yeah. I think it should've been `ON` not `WHERE`. Don't remember anymore, no time to re-check. – Craig Ringer May 18 '18 at 07:45
@Craig didn't mean to nitpick- I thought perhaps this was some hidden postgres trickery I didn't know about – user3871 May 21 '18 at 00:53
@Growler Totally fine. That's what comments are for. If it gave you pause, it confused someone else too. – Craig Ringer May 21 '18 at 13:56

Le Droid · Answer 2 · 2019-05-02T15:36:03.403

6

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.

edited May 02 '19 at 15:36

answered Apr 17 '19 at 22:30

Le Droid

4,534
3
37
32

@Thirumal Which version of Postgresql are you using? I use 9.6 & 10.7 and it's working fine. This exact sample may not work as it's missing group by and maybe details corresponding to your own table structure but the principle is there. – Le Droid May 02 '19 at 15:34

score 5 · Answer 3 · answered Jun 30 '22 at 22:44

5

Now in 2022 you can straight up do this:

SELECT dept, SUM(expense) FROM records 
WHERE (year, dept) IN (SELECT year, dept FROM otherTable);

answered Jun 30 '22 at 22:44

Dominic Napoleon

343
3
7

score 3 · Answer 4 · answered Sep 25 '17 at 15:38

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.

Postgres where clause over two columns from subquery

4 Answers4

Linked