Thread: [GENERAL] count records in two different table joined by
Hi guys!
I've got 2 tables, and I need to get some data between them.
test1:
test2:WITH account_status AS (selectCASEWHEN regdate = 1 THEN 'yes'WHEN regdate = 2 THEN 'no'from test1end as status_a)select status_a from account_status group by status_a
WITH user_status AS (selectCASEWHEN regdate = 1 THEN 'yes'WHEN regdate = 2 THEN 'no'from test1join test2 as t2 on t2.test1_id = t1.idend 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
Patrick
Hi,
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
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:test2:WITH account_status AS (selectCASEWHEN regdate = 1 THEN 'yes'WHEN regdate = 2 THEN 'no'from test1end as status_a)select status_a from account_status group by status_aWITH user_status AS (selectCASEWHEN regdate = 1 THEN 'yes'WHEN regdate = 2 THEN 'no'from test1join test2 as t2 on t2.test1_id = t1.idend as status_a)select status_a from user_status group by status_aIt 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
2017-07-07 22:32 GMT+12:00 Thomas Markus <t.markus@proventis.net>:
Hi,Am 07.07.17 um 12:16 schrieb Patrick B:one possibility is:Hi guys!I've got 2 tables, and I need to get some data between them.test1:test2:WITH account_status AS (selectCASEWHEN regdate = 1 THEN 'yes'WHEN regdate = 2 THEN 'no'from test1end as status_a)select status_a from account_status group by status_aWITH user_status AS (selectCASEWHEN regdate = 1 THEN 'yes'WHEN regdate = 2 THEN 'no'from test1join test2 as t2 on t2.test1_id = t1.idend as status_a)select status_a from user_status group by status_aIt 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
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:
I want this to work:
WITH account_status AS (selectCASEWHEN regdate = 1 THEN 'yes'WHEN regdate = 2 THEN 'no'end as status_a,count(t2.id) as t2_countfrom test1 as t1join test2 as t2 on t2.test1_id = t1.idend as status_a)selectstatus_a,t2_count,count(*)from account_status group by status_a, t2_count
I want this to work:WITH account_status AS (selectCASEWHEN regdate = 1 THEN 'yes'WHEN regdate = 2 THEN 'no'end as status_a,count(t2.id) as t2_countfrom test1 as t1join test2 as t2 on t2.test1_id = t1.idend as status_a)selectstatus_a,t2_count,count(*)from account_status group by status_a, t2_count
Usually aggregates and joins introduce the possibility of double-counting. Generally the simplest way is to write three queries.
WITH agg1 AS (),
agg2 AS ()
SELECT (SELECT agg1.result) AS ...,
(SELECT agg2.result) ...;
Depending on the data you can sometimes be a bit more efficient by doing:
SELECT agg(DISTSINCT col) AS ...
If since the distinct portion would remove the inherent duplication being introduced by the join.
David J.