CODEWITHSUNDEEP
mysqlsql
Joy Zhang
Joy Zhang

Reputation: 113

For SQL query, count with where clause error

In mysql database I have a table like this:

create table review(
reviewId varchar(12) primary key,
helpfulness double,
reviewRating integer)

I try to count helpful and unhelpful group by reviewRating and helpfulness where >=0.75 as unhelpfulness or where < 0.75 as helpfulness. How could I get the result like this?

unhelpfulness helpfulness reviewRating
5             2           1
4             2           2 
3             4           3

I trying to do like this, but it seems like count did not work and join is invalid at that position.

SELECT a.count AS HELPFUL, b.count AS UNHELPFUL 
FROM review a where helpfulness>=0.75 group by a.reviewRating
OUTER JOIN review b where helpfulness<0.75 group by b.reviewRating
on a.reviewRating = b.reviewRating

Upvotes: 5

Views: 52

Answers (2)

potashin
potashin

Reputation: 44581

You can implement conditional aggregation with case expression:

select a.reviewRating
     ,  count(case when helpfulness >= 0.75 then helpfulness end) as helpful
     ,  count(case when helpfulness < 0.75 then helpfulness end) as unhelpful 
from review a 
group by a.reviewRating

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

In Mysql you can do so by using sum with some condition or expression it will result as a boolean (0/1) and this way you can get the conditional count

SELECT a.reviewRating,
SUM(helpfulness>=0.75) AS HELPFUL,
SUM(helpfulness < 0.75)AS UNHELPFUL 
FROM review a 
GROUP BY a.reviewRating

Upvotes: 3

Related Questions