PostgreSQL find the sum of two queries from different tables-CodePudding

I have two queries that returns the total number of issuances and redemptions from two different tables.

This one returns issuances, around 18k

select
  count(*)
from
  issuances_extended
WHERE
  status = 'completed'

This one returns redemptions, around 7k

select
  count(*)
from
  redemptions_extended
WHERE
  status = 'completed'

I need the sum of them, I came up with this but this only returns 2, counting it wrong

with active_user as (
  select
    count(*) as issuance_count
  from
    issuances_extended
  where
    status = 'completed'
  UNION ALL
  select
    count(*) as redemption_count
  from
    redemptions_extended
  where
    status = 'completed'
  )
  select
    count(*)
  from
    active_user

What should I do?

CodePudding user response：

select 
  (select count(*) from issuances_extended WHERE status = 'completed')
  (select count(*) from redemptions_extended WHERE status = 'completed')
  AS result

CodePudding user response：

use sum()- instead of count() in outer query

with active_user as (
  select
    count(*) as issuance_count
  from
    issuances_extended
  where
    status = 'completed'
  UNION ALL
  select
    count(*) as redemption_count
  from
    redemptions_extended
  where
    status = 'completed'
  )
  select
   sum(issuance_count)
  from
    active_user