Search Postgresql Archives

Re: count records in two different table joined by

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





2017-07-07 22:32 GMT+12:00 Thomas Markus <t.markus@xxxxxxxxxxxxx>:
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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux