CODEWITHSUNDEEP
sqlsqlite
Ken1995
Ken1995

Reputation: 209

using aggregate function on a calculated column

Trying to use sum() on a newly created calculated column. The calc column name is not recognized in the sum function. Here's my code

select name, is_open,    
       CASE WHEN text like '%perfect%' or '%amazing%' or '%happy%' or '%delicious%'or '%fabulous%'or '%fantastic%'or '%kind%'   THEN 1
            WHEN text like '%hate%'or '%horrible%'or '%bad%'or '%angry%'or '%fantastic%'or '%expensive%'or '%disgusting%' THEN -1
       END sentiment_rating,    
       sum(sentiment_rating) as sum
from review as r left join
     business as b
     on b.id = r.business_id
where is_open is not Null and sentiment_rating is not Null
group by name
order by name ASC

Upvotes: 1

Views: 1540

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

It makes no sense to select both the unaggregated column and the aggregated column. So, just sum() the expression:

select name, is_open,    
       sum(CASE WHEN text like '%perfect%' or text like '%amazing%' or text like '%happy%' or text like '%delicious%' or text like '%fabulous%' or text like '%fantastic%' or text like '%kind%'   THEN 1
                WHEN text like '%hate%' or text like '%horrible%' or text like '%bad%' or text like '%angry%' or text like '%fantastic%'or '%expensive%' or text like '%disgusting%' THEN -1
           END) as sum_sentiment_rating 
from review as r left join
     business as b
     on b.id = r.business_id
where is_open is not Null and sentiment_rating is not Null
group by name, is_open
order by name ASC;

I'm not sure if you want one row per name or one row per name/is_open. I assumed the latter and added is_open to the GROUP BY.

Upvotes: 1

forpas
forpas

Reputation: 164139

All your LIKE conditions are syntactically wrong.
You can't have:

text like '%perfect%' or '%amazing%'....

the correct syntax is:

text like '%perfect%' or text like '%amazing%' ....

Then you must sum directly over the calculated with CASE column and include is_open in the grouping columns:

select name, is_open,    
  SUM(CASE 
    WHEN text like '%perfect%' or text like '%amazing%' or text like '%happy%' or text like '%delicious%' or text like '%fabulous%' or text like '%fantastic%' or text like '%kind%' THEN 1
    WHEN text like '%hate%' or text like '%horrible%' or text like '%bad%' or text like '%angry%' or text like '%fantastic%' or text like '%expensive%' or text like '%disgusting%' THEN -1
  END) as sumrating
from review as r left join
     business as b
     on b.id = r.business_id
where is_open is not Null and sentiment_rating is not Null
group by name, is_open
order by name ASC

Upvotes: 0

Related Questions