Re: [GENERAL] count records in two different table joined by

[David G. Johnston]

On Fri, Jul 7, 2017 at 3:49 AM, Patrick B  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.

Re: [GENERAL] count records in two different table joined by

[Patrick B]

2017-07-07 22:32 GMT+12:00 Thomas Markus :

> 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

Re: [GENERAL] count records in two different table joined by

[Thomas Markus]

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

[GENERAL] count records in two different table joined by

[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