From: | vinny <vinny(at)xs4all(dot)nl> |
---|---|
To: | Patrick B <patrickbakerbr(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org>, pgsql-general-owner(at)postgresql(dot)org |
Subject: | Re: count case when - PG 9.2 |
Date: | 2017-03-09 10:15:23 |
Message-ID: | 73ce6477fe28b4db13cb319f34a6ce96@xs4all.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2017-03-09 05:27, Patrick B wrote:
> Hi guys. How can I count using 'CASE WHEN'?
>
> Example:
>
>>> SELECT
>>
>>> CASE
>>
>>> WHEN AND c.regdate > EXTRACT(epoch FROM (now() - INTERVAL
>>> '14 day'))
>>
>>> THEN 'trial'
>>
>>> WHEN last_pay > EXTRACT(epoch FROM (now() - INTERVAL '37
>>> day'))
>>
>>> THEN 'paying'
>>
>>> END as account_status,
>>
>>> c.id [1]
>>
>>> FROM public.clients c
>>
>>> WHERE (
>>
>>> (last_pay > EXTRACT('epoch' FROM now() - '12
>>> Months'::INTERVAL))
>>
>>> )
>>
>>> ORDER BY 1
> I wanna know how many of 'trial' and 'paying' customers the query
> returns. can you guys please advice how to do it?
>
> Thanks
> Patrick
>
comparisons like "A>B" return a boolean. Booleans can be cast to
integers, and integers can be summed.
SUM((A>B)::int)
But depending on the situation, indexes etc it could be faster to run e
separate count query, you'll have to test that.
From | Date | Subject | |
---|---|---|---|
Next Message | mac pack | 2017-03-09 11:55:16 | Recovery damaged dump file |
Previous Message | Tom Lane | 2017-03-09 05:28:16 | Re: Unable to start postgresql |