不确定这是否可能,但我有以下内容
SELECT SUM(rating) as rating FROM details WHERE client_id = '$id'
rating
列包含用户 (1,2,3,4,5( 星的单个评分。我需要将它们全部相加才能计算平均值,但我也想得到该用户获得的每个分隔符的星数。
例如
用户有 3 条记录,评分为 (3,4,3(。在 3 条记录中 SUM 为 10 分,我平均得到 3.3 分。但我也想
3 stars = 2
4 stars = 1
是否可以使用一个查询来执行此操作?
如果我答对了你的问题,你可以使用 AVG(( 函数而不是 SUM((。
在下面的查询中,它计算这个client_id
的平均评级,并根据需要计算每颗星的数量。
SELECT
AVG(rating) as rating,
COUNT(CASE WHEN rating=1 THEN 1 END) as star_1,
COUNT(CASE WHEN rating=2 THEN 1 END) as star_2,
COUNT(CASE WHEN rating=3 THEN 1 END) as star_3,
COUNT(CASE WHEN rating=4 THEN 1 END) as star_4,
COUNT(CASE WHEN rating=5 THEN 1 END) as star_5
FROM
details
WHERE
client_id = ID_HERE
GROUP BY
client_id
SELECT client_id ,sum(if(rating='1',1,0)) onestar,sum(if(rating='2',1,0)) twostar,sum(if(rating='3',1,0)) threestar,sum(if(rating='4',1,0)) fourstar,sum(if(rating='5',1,0)) fivestar, SUM(rating) sumrate,AVG(rating) as avgrate FROM details WHERE client_id = '$id'
或适用于所有客户
SELECT client_id ,sum(if(rating='1',1,0)) onestar,sum(if(rating='2',1,0)) twostar,sum(if(rating='3',1,0)) threestar,sum(if(rating='4',1,0)) fourstar,sum(if(rating='5',1,0)) fivestar, SUM(rating) sumrate,AVG(rating) as avgrate FROM details WHERE group by client_id
目前还不清楚你到底在追求什么,但这可能会起作用:
SELECT rating, count(rating) FROM details WHERE client_id = '$id' GROUP BY rating;
另外,不确定如何获取$id,但是如果您从用户那里获取它,则应使用预准备语句,而不是直接将其插入SQL代码中。
你不需要对你的评分求和。我相信你想要的是关闭评级。我会建议这个。
这将对每个评级进行分组,然后告诉您该客户端被评级多少次。这也将帮助您考虑他们进行十进制投票的任何实例(即 4.5 星(
SELECT
concat(rating, ' Stars') as rating,
count(*) as count
FROM details
WHERE client_id = '$id'
Group by concat(rating, ' Stars');
我认为这应该可以做到。另外,你说你对它们求和只是为了取平均值。为什么不使用 AVG(( 来代替。
SELECT COUNT(rating), AVG(rating) as rating FROM details WHERE client_id = '$id' GROUP BY rating