this is my first question in StackOverflow.. hope someone can help.

why did I keep receiving the same value on 'Total Love Review' and 'Total Hate Review', even though I group by it by is_open?

here's my tables

business

review

here's the query

select business.is_open
    ,(select count (review.text)
    from review
    where review.text like '%love%') as 'Total Love Review'
    ,(select count (review.text)
    from review
    where review.text like '%hate%') as 'Total Hate Review'
,avg(business.stars) as 'Avg Stars'
from business
left join review on business.id = review.business_id
group by is_open

here's the result

expected results:

db<>fiddle here

CodePudding user response：

In addition, you don't need subqueries here. Use a conditional SUM() to count the reviews by type.

 select b.is_open
    , sum( case when r.text like '%love%' then 1 else 0 end ) AS 'Total Love Review'
    , sum( case when r.text like '%hate%' then 1 else 0 end ) AS 'Total Hate Review'
    , printf("%.4f", avg(b.stars)) as 'Avg Stars'
from  business b
        left join review r on b.id = r.business_id
group by b.is_open

Result:

is_open | Total Love Review | Total Hate Review | Avg Stars
------: | ----------------: | ----------------: | :--------
      0 |                 1 |                 1 | 3.5000   
      1 |                 6 |                 0 | 4.8333   

db<>fiddle here

CodePudding user response：

why did I keep receiving the same value...

The subqueries are not grouped by is_open. As a result, the fields displayed in the table will be the same. Perhaps try something like this:

    Select business.is_open
      , review.review_text As Review
      , count(review.review_text) As ReviewCount
      , Avg(stars) As AvgStars
    FROM business
    LEFT JOIN review
    ON business.business_id = review.business_id
    GROUP BY is_open
      , review_text

Which would give result like:

_____________________________________________________
| is_open | Review | ReviewCount | AvgStars         |
|_________|________|________________________________|
|    0    | Hate   |    5        | 1.4              |
|_________|________|________________________________|
|    0    | Love   |    12       | 4.33333333333333 |
|_________|________|________________________________|
|    1    | Hate   |    9        | 1.44444444444444 |
|_________|________|________________________________|
|    1    | Love   |    19       | 4.42105263157895 |
_____________________________________________________