Count if avg is below/above X-CodePudding

I am trying to get the number of 'critics' and 'promoters' from average of ratings from a joined table on a specific group of questions

     SELECT category
          , SUM( IF( round(avg(items.value) ) <= 6, 1, 0) ) AS critics
          , SUM( IF( round(avg(items.value) ) >= 9, 1, 0) ) AS promoters
     FROM reviews
     INNER JOIN items 
             ON reviews.id = items.review_id 
            AND items.question_id in (1, 2, 4)
     GROUP BY category

However I get the error:

General error: 1111 Invalid use of group function

CodePudding user response：

I think you should try with using having with it, something like below:

SELECT
   category,
   COUNT(items.id) AS critics
  FROM reviews
   INNER JOIN items ON reviews.id = items.review_id AND 
   items.question_id IN (1, 2, 4)
  GROUP BY category 
  HAVING ROUND(AVG(items.value)) <= 6

CodePudding user response：

First retrieve category wise rounded average value and then apply condition either it is critics and promoters.

   -- MySQL
   SELECT t.category
        , CASE WHEN t.avg_value <= 6
                  THEN 1
               ELSE 0
          END critics
        , CASE WHEN t.avg_value >= 9
                  THEN 1
               ELSE 0
          END promoters
   FROM (SELECT category
              , ROUND(AVG(items.value)) avg_value
         FROM reviews
         INNER JOIN items 
                 ON reviews.id = items.review_id 
                AND items.question_id IN (1, 2, 4)
         GROUP BY category) t

Please check this url for finding out pseudocode https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2679b2be50c3059c73ab9754c612179c