SQL: subquery for unary table-CodePudding

I'm trying to write a subquery that groups identical TickerSymbols togather as well as TickerSentiment that is between two values e.g. 0.0001 and 1

Sample Rows

TickerID    CommentID   TickerSymbol    TickerSentiment
3           3           DTE             0
4           3           SPY             0
7           6           MATX            -0.5574
9           8           ETSY            -0.5216
12          11          ROKU            -0.0926
14          13          ROKU            -0.7351
15          14          BROKU           0
17          16          SPY             -0.1531
18          17          CHGG            0.3612
29          28          AMP             0

Query:

SELECT TickerSymbol,
  (
        SELECT count(p.TickerSymbol) 
        FROM Ticker p
        WHERE p.TickerSymbol IS NOT "NONE" AND p.TickerSentiment BETWEEN 0.000000001 and 1 
    ) as "Positive Sentiment"
       
FROM Ticker
WHERE TickerSymbol IS NOT "NONE"

Output

TickerSymbol    Positive Sentiment
DTE             3573
SPY             3573
MATX            3573
ETSY            3573
ROKU            3573
ROKU            3573
BROKU           3573
SPY             3573

Desired Output

TickerSymbol    Positive Sentiment
DTE             101
SPY             46
MATX            24
ETSY            91
ROKU            24
BROKU           51

CodePudding user response：

You sample code will not work in MySQL

But i think you are looking for

SELECT DISTINCT t.TickerSymbol, pos_stat as 'Positive Sentiment'       
FROM Ticker t INNER JOIN 
  (
        SELECT p.TickerSymbol , COUNT(*) as pos_stat
        FROM Ticker p
        WHERE p.TickerSymbol NOT LIKE  'NONE' AND p.TickerSentiment BETWEEN 0.000000001 and 1 
        GROUP BY p.TickerSymbol
    ) t1 ON t.TickerSymbol = t1.TickerSymbol
WHERE TickerSymbol  NOT LIKE 'NONE'

CodePudding user response：

The problem is that you forget to specify related condition in your subquery. That's why your result would all be the same.
online sample: https://www.db-fiddle.com/f/htMM5HutjHrKkGEx7TPR4j/0

SELECT Distinct TickerSymbol,
  (
        SELECT count(p.TickerSymbol) 
        FROM Ticker p
        WHERE p.TickerSentiment BETWEEN 0.000000001 and 1 
                and Ticker.TickerSymbol = p.TickerSymbol
    ) as "Positive Sentiment"
FROM Ticker
WHERE TickerSymbol != "NONE"

Second problem is when you compare string, you should use != instead of is not. Like @nbk said it is not a valid sql in MySQL. The doc mention IS is using for boolean or null comparison.
https://dev.mysql.com/doc/refman/8.0/en/expressions.html#expression-syntax