2017-07-07 22:32 GMT+12:00 Thomas Markus <t.mar...@proventis.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