MySQL GROUP BY column and COUNT another column conditionally in the same table

Question

I am trying to query a table reviews from a MySQL database. Each row represents a review made on a product. The data for the relevant columns is stored like this:

{ product_id: 25, rating: 1, recommend: 'false' },
{ product_id: 25, rating: 4, recommend: 'true' },
{ product_id: 25, rating: 3, recommend: 'true' },
{ product_id: 25, rating: 2, recommend: 'false' },
{ product_id: 25,  rating: 1, recommend: 'false'}

I need to get the total of each type of rating (eg. how many reviews gave it a ‘1’), and then count how many reviews have recommend ‘true’ and how many have recommend ‘false’. I would like the query to return data like this:

{ product_id: 25, rating: 1, total: 2, recommendTrue: 2, recommendFalse: 3},
{ product_id: 25, rating: 2, total: 1, recommendTrue: 2, recommendFalse: 3},
{ product_id: 25, rating: 3, total: 1, recommendTrue: 2, recommendFalse: 3},
{ product_id: 25, rating: 4, total: 1, recommendTrue: 2, recommendFalse: 3},

Technically I don’t need the recommendTrue and recommendFalse to be on every line, but because I am grouping by the type of rating, the redundancy is fine.

When I use this query (A):

'SELECT product_id, rating, COUNT(rating) as total'
' ' + 'FROM reviews' 
' ' + 'WHERE reviews.product_id = ?' 
' ' + 'GROUP BY rating' 
' ' + 'LIMIT 50'

I get back part of the desired result:

{ product_id: 25, rating: 1, total: 1 },
{ product_id: 25, rating: 3, total: 1 },
{ product_id: 25, rating: 2, total: 3 },
{ product_id: 25, rating: 5, total: 1 },
{ product_id: 25, rating: 4, total: 2 }

I now need to count the total number of True and False recommends amongst all the reviews for product_id 25.

I am trying this query (B):

"SELECT r.product_id, COUNT(r.recommend='false') as F, COUNT(r.recommend='true') as T"
" " + "FROM reviews AS r"
" " + "WHERE r.product_id = ?"
" " + "GROUP BY r.rating"
" " + "LIMIT 50"

And getting this result:

{ product_id: 25, F: 1, T: 1, rating: 1, total: 1 },
{ product_id: 25, F: 1, T: 1, rating: 3, total: 1 },
{ product_id: 25, F: 3, T: 3, rating: 2, total: 3 },
{ product_id: 25, F: 1, T: 1, rating: 5, total: 1 },
{ product_id: 25, F: 2, T: 2, rating: 4, total: 2 }

The recommends need to be counted separately for True and False but should not be counted separately for rating. The way I am writing it, it is counting ALL the recommends (true and false) per rating.

Instead of COUNT(r.recommend='false') as F try SUM(CASE WHEN r.recommend='false' THEN 1 ELSE 0 END) as F. — FanoFN
– FanoFN, Commented Feb 14, 2022 at 6:38
The data for the relevant columns is stored like this This value is not correct for MySQL. Provide this as CREATE TABLE + INSERT INTO scripts for unambiguity. — Akina
– Akina, Commented Feb 14, 2022 at 6:46

slaakso · Accepted Answer · 2022-02-14 07:53:44Z

2

You have two separate queries: one for the true/false and one for the ratings.

You can combibe the two with a join statement. The query in the join will calculate the true/false and then the result is combined with the ratings query:

select product_id, rating, count(*), t, f
from reviews 
  join (
    select 
      sum(if(recommend='true', 1, 0)) as t, 
      sum(if(recommend='false', 1, 0)) as f
    from reviews
    where product_id=?
  ) as q
where product_id=?
group by product_id, rating, t, f

See db-fiddle.

answered Feb 14, 2022 at 7:53

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Meredith White Over a year ago

Thank you so much ! This worked perfectly. I just had to remember to write the query args like this : queryArgs = [productId, productId] so that there would be one for each ? Thank you again for this solution.

FanoFN · Accepted Answer · 2022-02-15 01:00:56Z

1

If you're on MySQL 8+ that supports window functions, you can try with SUM() OVER () function for the base query, then make that as a subquery to do the COUNT() and grouping. Something like this:

SELECT product_id, rating,
       COUNT(product_id) AS total,
       recommendTrue,
       recommendFalse
FROM
(SELECT product_id, rating,
       SUM(recommend='true') OVER () AS recommendTrue,
       SUM(recommend='false') OVER () AS recommendFalse
FROM reviews r
WHERE r.product_id = '25') A
GROUP BY product_id, rating, recommendTrue, recommendFalse

Demo fiddle

answered Feb 15, 2022 at 1:00

FanoFN

7,1442 gold badges16 silver badges35 bronze badges

Collectives™ on Stack Overflow

MySQL GROUP BY column and COUNT another column conditionally in the same table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related