Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

For SQL query, count with where clause error

Tags:

sql

mysql

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
like image 959
Joy Zhang Avatar asked Oct 19 '22 12:10

Joy Zhang


1 Answers

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
like image 125
M Khalid Junaid Avatar answered Oct 31 '22 11:10

M Khalid Junaid