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

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.

CodePudding user response：

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.

CodePudding user response：

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