10 clarify, add link
source | link

Or various other expressions. Performance is almost identical. COUNT() is typically very slightly faster than SUM(). Unlike SUM() and like Paul already commented, COUNT() never returns NULL, which may be convenient. Related:

The alternativeSince aggregatePostgres 9.4 there's also the FILTER clause introduced with Postgres 9.4 is actually faster. Details:

It's faster than all of the above by around 5 - 10 %:

Or various other expressions. Performance is almost identical. COUNT() is typically very slightly faster than SUM().

The alternative aggregate FILTER clause introduced with Postgres 9.4 is actually faster by around 5 - 10 %:

Or various other expressions. Performance is almost identical. COUNT() is typically very slightly faster than SUM(). Unlike SUM() and like Paul already commented, COUNT() never returns NULL, which may be convenient. Related:

Since Postgres 9.4 there's also the FILTER clause. Details:

It's faster than all of the above by around 5 - 10 %:

9 typos
source | link
COUNT(*) FILTER (WHERE amount > 50100)

I ran a new series of tests for Postgres 10, including the aggregate FILTER clause and demonstrating the role of an index for small and big counts.

CREATE TABLE tbl (
   tbl_id int
 , amount  int NOT NULL
);

INSERT INTO tbl
SELECT g, (random() * 150)::int
FROM   generate_series (1, 1000000) g;

-- only relevant for the last test
CREATE INDEX ON tbl (amount);
COUNT(*) FILTER (WHERE amount > 50)

I ran a new series of tests for Postgres 10, including the aggregate FILTER and demonstrating the role of an index for small and big counts.

CREATE TABLE tbl (
   tbl_id int
 , amount  int NOT NULL
);

INSERT INTO tbl
SELECT g, (random() * 150)::int
FROM   generate_series (1, 1000000) g;

-- only relevant for the last test
CREATE INDEX ON tbl (amount);
COUNT(*) FILTER (WHERE amount > 100)

I ran a new series of tests for Postgres 10, including the aggregate FILTER clause and demonstrating the role of an index for small and big counts.

CREATE TABLE tbl (
   tbl_id int
 , amount int NOT NULL
);

INSERT INTO tbl
SELECT g, (random() * 150)::int
FROM   generate_series (1, 1000000) g;

-- only relevant for the last test
CREATE INDEX ON tbl (amount);
8 added 30 characters in body
source | link

In PostgreSQLPostgreSQL (and other RDBMS that support the boolean type) you can use the boolean result of the test directly. Cast it to integer and SUM():

Actual times vary quite a bit due to background noise and specifics of the test bed. Showing typical best times from a bigger set of tests. These two cases should capture the essence:

In PostgreSQL (and other RDBMS that support the boolean type) you can use the boolean result of the test directly. Cast it to integer and SUM():

Actual times vary quite a bit due to background noise. Showing typical best times from a bigger set of tests. These two cases should capture the essence:

In PostgreSQL (and other RDBMS that support the boolean type) you can use the boolean result of the test directly. Cast it to integer and SUM():

Actual times vary quite a bit due to background noise and specifics of the test bed. Showing typical best times from a bigger set of tests. These two cases should capture the essence:

7 big update with new benchmark
source | link
6 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link
5 add pg 9.4
source | link
4 typos
source | link
3 add another nice variant, format
source | link
2 typo, trim some noise, clean up
source | link
1
source | link