From: | Yasin Sari <yasinsari81(at)googlemail(dot)com> |
---|---|
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 21:17:07 |
Message-ID: | CADhAfgO6_DDuiCeibqwqXh_EJqK3Zp4L-cE6k47FKrP-J0_UwA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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,
c <http://c.id/>ount(*)
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(at)gmail(dot)com> şunu
yazdı:
> 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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2017-03-09 21:18:28 | Re: Unable to start postgresql |
Previous Message | David G. Johnston | 2017-03-09 21:05:59 | Re: count case when - PG 9.2 |