Re: count case when - PG 9.2

2017-03-09 23:15 GMT+13:00 vinny <vinny(at)xs4all(dot)nl>:

> 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.
>

Could you please guys give me a query as an example?

Thanks
P.