按另一列中的值对同一列执行AVG单个sql查询 - AVG on same column by values in another column single sql query

AVG on same column by values in another column single sql query

本文关键字：一列 AVG 执行单个 sql 查询 | 更新日期: 2023-09-27

我想通过不同列的单sql查询中的值在同一列上找到AVG。

table - question_rating
review_id
question_id
rating

每个评论都有多个16个问题和评级
question_id的值是1到16
额定值是从1到5

我想找到一组问题的平均值作为

所需输出-

qustion_id     rating
-------------------------
g1               4.4
g2               3.7
g3               5.6

g1是一组问题（1,3）g2是一组问题（2,6）g3是一组问题（7,8）….g8

伪代码

 select (if(qustion=1 and question=3) as g1,
if(qustion=2 and question=6) as g2), ..
)avg(rating of respective group) from question_rating

我知道可以通过单独查询来完成，但我想通过单个查询来知道。

或者通过php等找到此类输出的任何简单方法。

您应该制作一个表question_groups

question_id  group_id
q1          g1
q2          g2
q3          g1
q4          g3
q5          g3
q6          g2

选择将是

select group_id, avg(rating)
from question_rating a
  join question_groups b on (a.question_id= b.question_id)
group by group_id

SELECT
  CASE question_id 
  WHEN 1 THEN 'g1'
  WHEN 3 THEN 'g1'
  WHEN 2 THEN 'g2'
  WHEN 4 THEN 'g2'
  ELSE 'others' END AS qGroup,
  AVG(rating) AS AvgRating
FROM question_rating
GROUP BY 
  CASE question_id 
  WHEN 1 THEN 'g1'
  WHEN 3 THEN 'g1'
  WHEN 2 THEN 'g2'
  WHEN 4 THEN 'g2'
  ELSE 'others' END