InsideDarkWeb.com

Counting the number of mention depending on sentiment SQL

Apologies if this is a duplicate of anything, I wasn’t finding answers which particularly did what I wanted.

I’m trying to write a SQL query which will return the count of rows which contain a positive, negative or neutral sentiment on one of the candidates in the dataset.

Here is a screenshot for reference

List of column names, for reference the most important ones are candidate and sentiment

Sentiment is one column but the values in it define the tweet to be positive, negative, or neutral. my goal is to have the query return something like this

table depicting the candidate name and counts of how many times they were mentioned positively, negatively, and neutrally

if anyone could give me an example on how to do this, I’d appreciate!

Stack Overflow Asked on November 15, 2021

2 Answers

2 Answers

try using specific COUNT() functions in your query like this.

SELECT name as `Candidate Name`, 
    COUNT(CASE WHEN sentiment='Negative' THEN 1 END) AS `Negative`,
    COUNT(CASE WHEN sentiment='Positive' THEN 1 END) AS `Positive`,
    COUNT(CASE WHEN sentiment='Neutral' THEN 1 END) AS `Neutral`,
    COUNT(*) AS `Total`

FROM [table]
    
GROUP BY candidate

Answered by Nik Srinivas on November 15, 2021

I like using IF()'s or CASE WHEN's to solve this type of thing. Pivots are sometimes time consuming to think through.

SELECT
  Name as CandidateName,
  SUM(IF(Sentiment = 'N', 1, 0)) as Negative,
  SUM(IF(Sentiment = 'Y', 1, 0)) as Positive,
  SUM(IF(Sentiment = 'N', 1, 0)) as Neutral
  COUNT(*) as Total
FROM [TABLE]
GROUP BY
  Name

To use t-SQL, or to just use CASE WHEN's, that same code could look like:

SELECT
  Name as CandidateName,
  SUM(CASE WHEN Sentiment = 'N' THEN 1 ELSE 0 END) as Negative,
  SUM(CASE WHEN Sentiment = 'Y' THEN 1 ELSE 0 END) as Positive,
  SUM(CASE WHEN Sentiment = 'N' THEN 1 ELSE 0 END) as Neutral
  COUNT(*) as Total
FROM [TABLE]
GROUP BY
  Name

Answered by ImTryingMyBest on November 15, 2021

Add your own answers!

Related Questions

Update Subform based on Form Combo Box

0  Asked on December 10, 2020 by hazat-bangurah

   

Determine if __getattr__ is method or attribute call

3  Asked on December 10, 2020 by robin-orheden

   

How to implement category axis in matplotlib?

0  Asked on December 10, 2020 by fgg1991

   

Pre-fill the checkbox with url

2  Asked on December 10, 2020 by sevenine

       

Shell=false generates OsError

1  Asked on December 10, 2020 by azza-abidi

       

LINQ query orderBy a subquery

2  Asked on December 9, 2020 by golfbravo

       

Avast blocks pip and Pyinstaller

1  Asked on December 9, 2020 by dripis

         

Trying to grasp callbacks javascript

2  Asked on December 9, 2020 by justaordinarymonkey

 

Typescript get distinct values from an object

3  Asked on December 9, 2020 by nikhila-reddy-c

     

Strip n from list created from txt file

1  Asked on December 9, 2020 by nataku62

       

Ask a Question

Get help from others!

© 2021 InsideDarkWeb.com. All rights reserved.