Re: count records in two different table joined by

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

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