From: | Patrick B <patrickbakerbr(at)gmail(dot)com> |
---|---|
To: | Thomas Markus <t(dot)markus(at)proventis(dot)net> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: count records in two different table joined by |
Date: | 2017-07-07 10:49:54 |
Message-ID: | CAJNY3iudYAFTG-bS33WBH9i0nQLpCN-PkXvbUrAJ2Jv03=4Z9g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2017-07-07 22:32 GMT+12:00 Thomas Markus <t(dot)markus(at)proventis(dot)net>:
> Hi,
>
> Am 07.07.17 um 12:16 schrieb Patrick B:
>
> Hi guys!
>
> I've got 2 tables, and I need to get some data between them.
>
> test1:
>
> WITH account_status AS (
> select
> CASE
> WHEN regdate = 1 THEN 'yes'
> WHEN regdate = 2 THEN 'no'
> from test1
> end as status_a
> )
>
> select status_a from account_status group by status_a
>
> test2:
>
> WITH user_status AS (
> select
> CASE
> WHEN regdate = 1 THEN 'yes'
> WHEN regdate = 2 THEN 'no'
> from test1
> join test2 as t2 on t2.test1_id = t1.id
> end as status_a
> )
>
> select status_a from user_status group by status_a
>
>
> It works fine.. but I would like to get that data in one single query..
> How can I do that?
>
> I'm using Postgres 9.3.
>
> Thanks!
> Patrick
>
>
> one possibility is:
>
> select distinct
> case
> when regdate = 1 THEN 'yes'
> when regdate = 2 THEN 'no'
> end as status_a
> , t2.id is null as test2exists
> from test1 t1 left join test2 t2 on t2.test1_id = t1.id
>
> hth
> Thomas
>
>
hmmm... not really
I want this to work:
WITH account_status AS (
select
CASE
WHEN regdate = 1 THEN 'yes'
WHEN regdate = 2 THEN 'no'
end as status_a,
count(t2.id) as t2_count
from test1 as t1
join test2 as t2 on t2.test1_id = t1.id
end as status_a
)
select
status_a,
t2_count,
count(*)
from account_status group by status_a, t2_count
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2017-07-07 10:59:03 | Re: psql doesn't pass on exported shell environment functions |
Previous Message | Thomas Markus | 2017-07-07 10:32:02 | Re: count records in two different table joined by |