mysql group by multiple columns with count - 不分组所有答案

Question

我按两列分组并试图弄清楚为什么两组精确的列没有加在一起。我已经删除了所有其他数据，并试图让一组数据正常工作，理论上这将使其他数据集也能正常工作。这是我得到的示例输出:

Question | Answer      | Count
Waiver   | Waiver Info |  11
Waiver   | Waiver Info |   7

预期输出:

Question | Answer      | Count
Waiver   | Waiver Info |  18

查询是:

SELECT question, answer, count(*) as `count` FROM orderitems oi
JOIN orders o ON o.orderid = oi.oid 
JOIN ordermeta om ON om.groupid = oi.groupid
WHERE question = 'waiver:'
group by question, answer
order by question, answer

还会有其他问题和答案被分成更多信息和计数，所以我必须同时按列分组和按列排序.​​..问题和答案完全相同，所以我想不通弄清楚他们为什么不将这两行加在一起。

Answer 1

您可能在字段中有空格，这就是为什么它会向您显示两行而不是单个结果。分组前可以用空字符串替换空格。

方法如下:

SELECT question, answer, count(*) as `count` FROM orderitems oi
JOIN orders o ON o.orderid = oi.oid 
JOIN ordermeta om ON om.groupid = oi.groupid
WHERE question = 'waiver:'
group by replace(question,' ',''), replace(answer,' ','')
order by question, answer