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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [GENERAL] count records in two different table joined by
Дата
Msg-id CAKFQuwYKz8YQu+hntWx0EFh-dhPfTA62MRm1dpSE8b8AtTZhNg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [GENERAL] count records in two different table joined by  (Patrick B <patrickbakerbr@gmail.com>)
Список pgsql-general
Дерево обсуждения
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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: [GENERAL] Postgresql - commercial version
Следующее
От: Krithika Venkatesh
Дата:
Сообщение: Re: [GENERAL] Postgresql - commercial version