0

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

  • Unrelated, but: distinct is not a function. distinct a is exactly the same thing as distinct (a) – a_horse_with_no_name Mar 25 at 6:36
  • @Akina, I have changed the GROUP BY name also. Excuse me that was by mistake. – bunny sunny Mar 25 at 6:53
0
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;
  • Thank you @Akina. It worked perfectly to my scenario and got desired output. – bunny sunny Mar 25 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.