MySQL 如何在一个查询中返回 COUNT 总数以及使用 GROUP BY 的 COUNT

Question

我有以下查询:

SELECT location, COUNT( location ) AS Total, (

SELECT location, COUNT( location ) AS Responses
FROM  `trespondent` 
WHERE completion_status IN ('Started',  'Complete')
GROUP BY location
)

FROM  `trespondent` 
GROUP BY location

这会返回一个错误:

Operand should contain 1 column(s)

这是完全正确的，因为会有不止一行。

我想要在单个查询中实现的目标是返回“位置”中每个选项的总数，然后返回“位置”中每个选项的“已开始”或“已完成”的数量.

如果可以在单个查询中实现这一点，请提供任何建议，如果可以，欢迎任何指点。

Answer 1

您可以使用 UNION 子句更改查询，如下所示，以使用两个版本的查询并获取总体结果

SELECT location, COUNT( location ) AS Total
FROM  `trespondent`
GROUP BY location

UNION ALL

SELECT location, COUNT( location ) AS Responses
FROM  `trespondent` 
WHERE completion_status IN ('Started',  'Complete')
GROUP BY location

或者可能使用JOIN来连接两个查询结果，例如

SELECT t1.location as T1location, 
COUNT( t1.location ) AS Total,
tab.Responses,
tab.location as tablocation
FROM  `trespondent` t1 
JOIN
(
SELECT location, COUNT( location ) AS Responses
FROM  `trespondent` 
WHERE completion_status IN ('Started',  'Complete')
GROUP BY location
) tab
ON t1.location = tab.location
GROUP BY t1.location