How to perform count(), count(distinct) in the same output using POSTGRESQL

Question

I am trying to perform both count(distinct(keyword)) and count(keyword) from same tables. Here are the two queries.

Query-1

SELECT t1.count(distinct(keyword)) from keyword t1, sent t2,tweets t3,users t4 
WHERE t4.user_id='18839785' 
AND t1.id=t2.id AND t2.id=t3.id AND t3.user_id=t4.user_id AND t2.sent='Pos';
count 
-------
251
(1 row)

Query-2

SELECT t1.keyword,t1.count(keyword) FROM keyword t1, sent t2,tweets t3,users t4 
WHERE t4.user_id='18839785' AND t3.id=t4.user_id AND t2.id=t3.id 
AND t2.id=t1.id AND t2.sent='Pos' GROUP BY t1.keyword order by count(keyword) desc ;
 nlp_keyword_name | count 
 ------------------+-------
 happy | 5
 people | 4
 handsome | 4
 glad | 3
 forward | 3
 wishes | 3
 proud | 2
 (7 rows)

Query-3

SELECT distinct_drivers,positive_drivers,countt
from (
SELECT t1.count(distinct(keyword)) as distinct_drivers ,
t1.nlp_keyword_name as P_drivers,
t1.count(nlp_keyword_name) as countt
FROM keyword t1 LEFT JOIN sent t2 on t1.id=t2.id 
LEFT JOIN tweets t3 on t3.id=t2.id 
LEFT JOIN users t4 on t4.user_id=t3.user_id 
WHERE (t4.user_id)=('18839785') AND t2.sent='Pos' GROUP BY t1.keyword order by countt desc) T;

But I am getting my output as

distinct_drivers | positive_drivers | countt 
------------------+------------------+--------
 1 | happy | 5
 1 | people | 4
 1 | handsome | 4
 1 | glad | 3
 1 | forward | 3

Here after executing query-3 my output has to be below way which I am not getting like that.

 **desired_output:**
 distinct_drivers | positive_drivers | countt 
------------------+------------------+--------
 251 | happy | 5
 251 | people | 4
 251 | handsome | 4
 251 | glad | 3
 251 | forward | 3

Can anyone help me out in achieving desired output. I will be thankful. resources I have gone through Rolling sum / count / average over date interval

• user1822: Unrelated, but: distinct is not a function. distinct a is exactly the same thing as distinct (a)
• bunny sunny: @Akina, I have changed the GROUP BY name also. Excuse me that was by mistake.

Answer 1

WITH cte1 AS ( SELECT t1.keyword, t1.count(keyword) cnt
 FROM keyword t1, sent t2, tweets t3, users t4 
 WHERE t4.user_id='18839785' 
 AND t3.id=t4.user_id 
 AND t2.id=t3.id 
 AND t2.id=t1.id 
 AND t2.sent='Pos' 
 GROUP BY t1.keyword ),
 cte2 AS ( SELECT COUNT(DISTINCT keyword) cnt
 FROM cte1 )
SELECT cte2.cnt distinct_drivers, cte1.keyword positive_drivers, cte1.cnt countt
FROM cte1, cte2
ORDER BY cte1.cnt;

• bunny sunny: Thank you @Akina. It worked perfectly to my scenario and got desired output.