Creating indvidual columns with SQL

Question

I'm new to sql and very novice with this package. SQL is running in Watson DashDB. For the past few hours I've been struggling to find the correct code.

• The code is trying to accomplish a few things.
  1. Create a new view called SENTIMENT
  2. Join two tables together
  3. Have the new table show 4 columns with A. USER_SCREEN_NAME, B. Total Tweets, C. Postive SENTIMENT Count D. Negative SENTIMENT Count

The code below only creates 2 columns, I am needing 4. SPACEX_SENTIMENTS.SENTIMENT_POLARITY contain both Negative and Positive.

CREATE VIEW SENTIMENT
AS
(SELECT SPACEX_TWEETS.USER_SCREEN_NAME, SPACEX_SENTIMENTS.SENTIMENT_POLARITY
FROM dash015214.SPACEX_TWEETS
LEFT JOIN dash015214.SPACEX_SENTIMENTS ON 
SPACEX_TWEETS.MESSAGE_ID=SPACEX_SENTIMENTS.MESSAGE_ID);

SELECT USER_SCREEN_NAME, COUNT(1) tweetsCount
FROM dash015214.SENTIMENT
GROUP BY USER_SCREEN_NAME
HAVING COUNT (1)>1
ORDER BY COUNT (USER_SCREEN_NAME) DESC
FETCH FIRST 20 ROWS ONLY;

Answer 1

It looks like your view SENTIMENT has one row per tweet, with two columns: the user name and then a polarity column. From your comment, I assume the polarity column can have the values of either 'POSITIVE' or 'NEGATIVE'. I think you can get what you want with this query:

SELECT 
    USER_SCREEN_NAME, 
    COUNT(1) AS "Total Tweets",
    COUNT(CASE SENTIMENT_POLARITY WHEN 'POSITIVE' THEN 1 ELSE NULL END) AS "Positive Tweets",
    COUNT(CASE SENTIMENT_POLARITY WHEN 'NEGATIVE' THEN 1 ELSE NULL END) AS "Negative Tweets"
FROM 
    SENTIMENT
GROUP BY USER_SCREEN_NAME
HAVING COUNT(1) > 1
ORDER BY COUNT(1) DESC;

This will give you everyone with at least 2 tweets (is that what you want?), and tell you the number of tweets per user and how many were positive and how many were negative. Replace " with whatever your SQL uses to indicate column names.