Postgres Pro
Downloads
Home   >   mailing lists

Re: Function fixing - PostgreSQL 9.2 - Mailing list pgsql-general

From David G. Johnston
Subject Re: Function fixing - PostgreSQL 9.2
Date
Msg-id CAKFQuwb1bRVERP5zQVdV3Sc8rXs5HvjArQiCWqSqiGLeBb+4NA@mail.gmail.com
Whole thread Raw
In response to Function fixing - PostgreSQL 9.2  ("drum.lucas@gmail.com" <drum.lucas@gmail.com>)
Responses Re: Function fixing - PostgreSQL 9.2  ("drum.lucas@gmail.com" <drum.lucas@gmail.com>)
List pgsql-general
Tree view
On Mon, Feb 29, 2016 at 2:56 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:

Question:

Payments in a Pending state cannot be invoiced and are excluded from the Invoice Runs section, but they are showing in the count mechanic.

How can I solve this?


​In 9.2 you probably need to convert the count into a conditional sum:

SELECT sum(CASE WHEN <boolean> THEN 1 ELSE 0 END) FROM data;

You can probably do the same with count since it excludes nulls.

SELECT count(NULLIF(invoice_status, 'Pending') FROM invoices;

​9.4 introduced a FILTER clause for Aggregate Expressions that can do this much more cleanly and efficiently.​


David J.

pgsql-general by date:

Previous
From: "drum.lucas@gmail.com"
Date:
Subject: Function fixing - PostgreSQL 9.2
Next
From: Kevin Grittner
Date:
Subject: Re: multicolumn index and setting effective_cache_size using human-readable-numbers