Postgresql: JOIN two SELECT/COUNT queries and derive additional column-CodePudding

I have a table with the following structure:

 name | version | processed | processing | updated  | ref_time 
------ --------- ----------- ------------ ---------- ----------
 abc  |       1 | t         | f          | 27794395 | 27794160
 def  |       1 | t         | f          | 27794395 | 27793440
 ghi  |       1 | t         | f          | 27794395 | 27793440
 jkl  |       1 | f         | f          | 27794395 | 27794160
 mno  |       1 | t         | f          | 27794395 | 27793440
 pqr  |       1 | f         | t          | 27794395 | 27794160

I can use the following query to count the total number within each ref_time:

SELECT ref_time, COUNT (*) AS total FROM (SELECT * FROM status_table) AS _ GROUP BY ref_time;

 ref_time | total 
---------- -------
 27794160 |  2259
 27793440 |  2259

And the following query to count the total number within each ref_time where processed=true:

SELECT ref_time, COUNT (*) AS processed FROM (SELECT * FROM status_table WHERE processed=true) AS _ GROUP BY ref_time;

 ref_time | processed 
---------- -----------
 27794160 |      1057
 27793440 |      2259

I then try to merge the information using an INNER JOIN on ref_time:

SELECT * FROM
(SELECT ref_time, COUNT (*) AS total FROM (SELECT * FROM status_table) AS _ GROUP BY ref_time) result_total
INNER JOIN
(SELECT ref_time, COUNT (*) AS processed FROM (SELECT * FROM status_table WHERE processed=true) AS _ GROUP BY ref_time) result_processed
ON result_total.ref_time = result_processed.ref_time;

 ref_time | total | ref_time | processed 
---------- ------- ---------- -----------
 27794160 |  2259 | 27794160 |      1057
 27793440 |  2259 | 27793440 |      2259

First question: how do I avoid the duplicated ref_time column?

Second question: how do I add an additional percent column derived as (100 * processed / total) (to one d.p.), i.e. to give:

 ref_time | total | processed | percent 
---------- ------- ----------- ---------
 27794160 |  2259 |      1057 |    46.8
 27793440 |  2259 |      2259 |   100.0

Third question: is there a more efficient way to do this... can I avoid making two separate SELECT queries?

CodePudding user response：

SELECT ref_time,count(*)as total,
SUM
(
  CASE
   WHEN processed='t' then 1
   else 0
  END
)processed
FROM YOUR_TABLE
GROUP BY ref_time

CodePudding user response：

with main as (
    select
    ref_time,
    sum(case when processed = 'true' then 1 else 0 end ) as total_processed,
    count(*) as total
    
    from <table_name>
    group by 1
)
select *, round((total_processed::numeric / nullif(total::numeric,0))  * 100),2) as percent from main