PostgreSQL 9.3 – How to Count Rows from Two Tables in the Same Query

countctepostgresql

I've got two tables that I need a count on both of them.

table1: id
table2: cid (table1.id = table2.cid) -- Join can be done by this.

I already got two queries that I'm using, and wanna put them in one single query.

Query related to table1:

WITH c_status AS (
select 
CASE 
  WHEN regdate = 1 THEN 'yes'
  WHEN regdate = 2 THEN 'no'
  from table1
  end as status_1
)

select status_1, count(*) AS c_status_count from c_status group by status_1

OUTPUT:

yes 548
no  2269

Query related to table2:

WITH u_status AS (
select 
CASE 
  WHEN regdate = 1 THEN 'yes'
  WHEN regdate = 2 THEN 'no'
  from table2
  end as status_2
)

select status_2, count(*) AS u_status_count from u_status group by status_2

OUTPUT:

yes 564256
no  31452345234

Question:

How can I put those two queries together? I want one single query reporting both counts.

UPDATE: Desired output:

u_status yes    548
u_status no     2269
c_status yes    564256
c_status no 31452345234

Best Answer

What you want can be achived in the following fashion:

WITH 
c AS  -- This part of CTE for counting on table 1
(
    -- This select will just return ONE row
    SELECT
        sum(case when regdate = 1 then 1 end) AS c_status_yes,
        sum(case when regdate = 2 then 1 end) AS c_status_no
    FROM
        table1 
),
u AS   -- And this one for table 2
(
    -- This select will also return just one row
    SELECT
        sum(case when regdate = 1 then 1 end) AS u_status_yes,
        sum(case when regdate = 2 then 1 end) AS u_status_no
    FROM
        table2 
)
-- And you just put everything together
SELECT
    c_status_yes,
    c_status_no,
    u_status_yes,
    u_status_no
FROM
    c, u ;  -- This is a 1x1 CROSS JOIN, don't worry

NOTE: the little trick: sum(case when regdate = 1 then 1 end) is in fact counting how many times the regdate = 1 condition is met; when regdate <> 1 the case statement returns NULL, effectively not summing 1 in that case.

You can also use a count(*) filter instead of a sum(case when ...), although I am not sure this could already be done in version 9.3 (I've tested on 9.4):

WITH c AS
(
    SELECT
        count(*) filter (where regdate = 1) as c_status_yes, 
        count(*) filter (where regdate = 2) as c_status_no
    FROM
        table1 
)
, u AS
(
    SELECT
        count(*) filter (where regdate = 1) as u_status_yes, 
        count(*) filter (where regdate = 2) as u_status_no
    FROM
        table2 
)
SELECT
    c_status_yes,
    c_status_no,
    u_status_yes,
    u_status_no
FROM
    c, u ;

dbfiddle here

Related Question