Thread: [GENERAL] count case when - PG 9.2

[GENERAL] count case when - PG 9.2

From

Patrick B

Date:

09 March 2017, 07:27:30

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

Re: [GENERAL] count case when - PG 9.2

From

"David G. Johnston"

Date:

09 March 2017, 08:02:40

On Wednesday, March 8, 2017, Patrick B <patrickbakerbr@gmail.com> 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
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

Turn that into a subquery and group by...

David J.

Re: [GENERAL] count case when - PG 9.2

From

vinny

Date:

09 March 2017, 13:15:23

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.

Re: [GENERAL] count case when - PG 9.2

From

Patrick B

Date:

10 March 2017, 00:01:24

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?

Thanks

Re: [GENERAL] count case when - PG 9.2

From

"David G. Johnston"

Date:

10 March 2017, 00:05:59

On Thu, Mar 9, 2017 at 2:01 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

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

Maybe if you describe exactly how you want the output to appear. And maybe tee things up by writing a query that gets close and with some example data. Something that can be copy-paste-executed

David J.

Re: [GENERAL] count case when - PG 9.2

From

Yasin Sari

Date:

10 March 2017, 00:17:07

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@gmail.com> şunu yazdı:

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?

Thanks
P.

Re: [GENERAL] count case when - PG 9.2

From

Patrick B

Date:

10 March 2017, 00:19:22

2017-03-10 10:17 GMT+13:00 Yasin Sari <yasinsari81@googlemail.com>:

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

Thanks Yasin! That worked.