1

I am trying to calculate percentages by joining 3 tables data to get the percentages of positive_count, negative_count, neutral_count of each user's tweets. I have succeeded in getting positive, negative and neutral counts, but failing to get the same as percentages instead of counts. Here is the query to get counts:

SELECT t1.u_id,count(*) as total_tweets_count
, (SELECT count(*) from t1,t2,t3 c WHERE  t1.u_id='18839785' AND 
t1.u_id=t2.u_id AND t2.ts_id=t3.ts_id AND t3.sentiment='Positive') as pos_count
, (SELECT count(*) from t1,t2,t3 WHERE  t1.u_id='18839785' AND 
t1.u_id=t2.u_id AND t2.ts_id=t3.ts_id AND t3.sentiment='Negative') as neg_count
, (SELECT count(*) from t1,t2,t3 WHERE  t1.u_id='18839785' AND 
t1.u_id=t2.u_id AND t2.ts_id=t3.ts_id AND t3.sentiment='Neutral') as neu_count 
FROM t1,t2,t3 WHERE t1.u_id='18839785' AND t1.u_id=t2.u_id AND 
t2.ts_id=t3.ts_id GROUP BY t1.u_id;

 u_id | total_tweets_count | pos_count | neg_count | neu_count 
 -----------------+--------------------+-----------+-----------+-----------
 18839785|         88 |        38 |        25 |        25
 (1 row)

Now I want the same in percentages instead of counts. I have written the query in the following way but failed.

SELECT total_tweets_count
,pos_count, round((pos_count * 100.0) / total_tweets_count, 2) AS 
pos_per
,neg_count, round((neg_count * 100.0) / total_tweets_count, 2) AS 
neg_per
,neu_count, round((neu_count * 100.0) / total_tweets_count, 2) AS 
neu_per 
FROM (SELECT count(*) as total_tweets_count
,count(a.u_id='18839785' AND a.u_id=b.u_id AND b.ts_id=c.ts_id AND 
c.sentiment='Positive') AS pos_count
,count(a.u_id='18839785' AND a.u_id=b.u_id AND b.ts_id=c.ts_id AND 
c.sentiment='Negative') AS neg_count
,count(a.u_id='18839785' AND a.u_id=b.u_id AND b.ts_id=c.ts_id AND 
c.sentiment='Neutral') AS neu_count

FROM t1,t2, t3 WHERE a.u_id='18839785' AND a.u_id=b.u_id AND 
b.ts_id=c.ts_id GROUP BY a.u_id) sub;

Can anyone help me out in achieving as percentages for each user data?

Your Answer

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

Browse other questions tagged or ask your own question.