count records in two different table joined by

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 <http://t1.id&gt;
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

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:

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

On Fri, Jul 7, 2017 at 3:49 AM, Patrick B <patrickbakerbr@gmail.com> wrote:

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

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