0

i am looking for elegant way to write PostgreSQL version of this query:

SELECT count(distinct a,b) / count(distinct c,d) FROM t1 GROUP BY e; 

It can be done via CONCAT function, but it this case i get huge performance drop.

Each count distinct can be written in this way:

SELECT count(*), e FROM (SELECT a, b, e FROM t1 GROUP BY a, b, e) as tmp GROUP BY e; 

AND

SELECT count(*), e FROM (SELECT c, d, e FROM t1 GROUP BY c, d, e) as tmp GROUP BY e; 

But i can't find how to combine them into one query to get final result

Here is create table to test syntax:

CREATE TABLE t1 ( a int, b int, c int, d int, e int ); 
0

1 Answer 1

1

Not sure about elegant, but looks like it can be done in this way:

SELECT e, c1/c2 FROM (SELECT count(*) as c1, e FROM (SELECT a, b, e FROM t1 GROUP BY a, b, e) as sub1 GROUP BY e) as tmp1 INNER JOIN (SELECT count(*) as c2, e FROM (SELECT c, d, e FROM t1 GROUP BY c, d, e) as sub2 GROUP BY e) as tmp2 USING (e) 
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.