PostgreSQL several COUNT DISTINCT in same query with GROUP BY on other field

Question

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 
);

Oleksandr Voytsekhovskyy · Accepted Answer · 2016-04-24 05:56:34Z

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)

answered Apr 24, 2016 at 5:56

Oleksandr Voytsekhovskyy

1761 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL several COUNT DISTINCT in same query with GROUP BY on other field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related