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