Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Fairly simple question that I can't seem to find an answer to - I'm working with unions and differences, and I would like to perform a COUNT on the results. Currently I'm having to pipe out to a file and "wc -l" the file (minus 4 for the Postgres printing). There's got to be a way to include COUNT...

SELECT tbl1.id EXCEPT (SELECT tbl2.id UNION tbl3.id); 

I would like to know the number of results after the set difference. Thanks for any input!

share|improve this question
2  
If you want only the count, why not do the following: SELECT count(id) FROM (SELECT tbl1.id EXCEPT (SELECT tbl2.id UNION tbl3.id)) a; –  dezso Jul 31 '12 at 12:25

2 Answers 2

up vote 2 down vote accepted

Here you go:

SELECT COUNT(*) FROM (
    SELECT tbl1.id
    FROM tbl1 EXCEPT
    (
    SELECT tbl2.id
    FROM tbl2

    UNION

    SELECT tbl3.id
    FROM tbl3
    )
) AS t;
share|improve this answer
    
Doesn't look correct. Seems to get the COUNT then EXCEPT some ids from that number. –  Martin Smith Jul 31 '12 at 12:32
1  
That'll teach me for not testing. Edited & that'll definitely work. –  Phil Jul 31 '12 at 12:35
3  
Nearly definitely. Subqueries specifying a derived table must ... be assigned a table alias name in postgres –  Martin Smith Jul 31 '12 at 12:38
    
I'll get my coat... :) –  Phil Jul 31 '12 at 12:38
2  
This will also work: SELECT COUNT(*) FROM (SELECT id FROM tbl1 EXCEPT SELECT id FROM tbl2 EXCEPT SELECT id FROM tbl3) AS t; –  ypercube Jul 31 '12 at 13:06

Another way to have the same result is:

SELECT COUNT(*) 
FROM 
  ( SELECT id FROM tbl1 
  EXCEPT 
    SELECT id FROM tbl2 
  EXCEPT 
    SELECT id FROM tbl3
  ) AS t ;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.