Re: count records in two different table joined by

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