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,
count(*)
FROM public.clients c
WHERE (
(last_pay > EXTRACT('epoch' FROM now() - '12 Months'::INTERVAL))
)
ORDER BY 1
2017-03-09 23:15 GMT+13:00 vinny <vinny@xs4all.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?ThanksP.
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.
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
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
c.id [1]
pgsql-general by date:
Соглашаюсь с условиями обработки персональных данных
