Search Postgresql Archives

Re: count case when - PG 9.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



if you want see account_status and the count()- try this:

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))
              ) 
               group by 1 
    ORDER BY 1 

10 Mar 2017 Cum, 00:02 tarihinde, Patrick B <patrickbakerbr@xxxxxxxxx> şunu yazdı:
2017-03-09 23:15 GMT+13:00 vinny <vinny@xxxxxxxxx>:
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. 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux