Re: [GENERAL] count case when - PG 9.2

Поиск
Список
Период
Сортировка
От vinny
Тема Re: [GENERAL] count case when - PG 9.2
Дата
Msg-id 73ce6477fe28b4db13cb319f34a6ce96@xs4all.nl
обсуждение исходный текст
Ответ на [GENERAL] count case when - PG 9.2  (Patrick B <patrickbakerbr@gmail.com>)
Ответы Re: [GENERAL] count case when - PG 9.2  (Patrick B <patrickbakerbr@gmail.com>)
Список 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.


В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [GENERAL] Unable to start postgresql
Следующее
От: mac pack
Дата:
Сообщение: [GENERAL] Recovery damaged dump file