Stack Overflow Asked on November 15, 2021
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
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
if anyone could give me an example on how to do this, I’d appreciate!
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP