3

In PostgreSQL 9.4 I have a table:

id p_id PK integer ----------------- 1 1 2 1 ............. 123122 2233

Is there a way to count all distinct p_id values in the table with only one query (without using subqueries). I'm free to use any window-function.

Improve this question
0

1 Answer 1

Reset to default
4

You can use the distinct modifier in the count function:

SELECT COUNT(DISTINCT p_id) FROM mytable
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Interesting, why does somenone downvote the post? Is it a bad way to do that?
Postgres and Hive are two databases where count() with a subquery using distinct is often faster than count(distinct). I sincerely doubt that the anonymous downvoter was thinking about this detail of the execution plan. This is the correct answer. And, the cases where count(distinct) produces a suboptimal execution plan are an opportunity for Postgres developers to further improve the database in the future.
@GordonLinoff I admit I did not know that about Postgres, thanks! Learnt something new today! I'll have to play around with my local database a bit to see how I can reproduce this.
@Mureinik . . . You can see the behavior on multi-processor servers. The key idea is that the "aggregation" part of a group by runs in parallel, but the "function" part of evaluating an aggregation function is serialized. This can have a big impact on performance.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.