将多个计数组合成一个查询组by

I have 3 SELECT statements I'd like to combine into one:

我有三个精选的陈述，我想合并成一个:

SELECT COUNT(A.id), CONCAT(B.fname,' ', B.lname) AS fullname 
FROM feedbacks A 
    INNER JOIN users B 
         ON A.userid = B.userid 
WHERE DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' 
GROUP BY fullname

SELECT COUNT(A.id), CONCAT(B.fname,' ', B.lname) AS fullname 
FROM feedbacks A 
    INNER JOIN users B 
        ON A.userid = B.userid 
WHERE status = 'C' 
AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' 
GROUP BY fullname`

SELECT COUNT(A.id), CONCAT(B.fname,' ', B.lname) AS fullname 
FROM feedbacks A 
    INNER JOIN users B ON A.userid = B.userid 
WHERE caused_change = 1 
AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' 
GROUP BY fullname

But combining them always seems to return an error: (Operand should contain 1 column(s))

但是组合它们总是会返回一个错误:(操作数应该包含1列)

SELECT 
(SELECT COUNT(A.id), CONCAT(B.fname,' ', B.lname) AS fullname FROM feedbacks A INNER JOIN users B ON A.userid = B.userid WHERE DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname) AS T1,  
(SELECT COUNT(A.id), CONCAT(B.fname,' ', B.lname) AS fullname FROM feedbacks A INNER JOIN users B ON A.userid = B.userid WHERE status = 'C' AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname) AS T2,  
(SELECT COUNT(A.id), CONCAT(B.fname,' ', B.lname) AS fullname FROM feedbacks A INNER JOIN users B ON A.userid = B.userid WHERE caused_change = 1 AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname) AS T3

So I tried removing the JOIN and GROUP from the individual statements to make:

因此，我试着从单个语句中删除JOIN和GROUP:

SELECT CONCAT(B.fname, ' ', B.lname) AS fullname,
(SELECT COUNT(A.id) FROM feedbacks A WHERE DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11') AS T1,  
(SELECT COUNT(A.id) FROM feedbacks A WHERE status = 'C' AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11') AS T2,
(SELECT COUNT(A.id) FROM feedbacks A WHERE caused_change = 1 AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11') AS T3
FROM feedbacks 
    INNER JOIN users B 
        ON feedbacks.userid = B.userid 
GROUP BY fullname

But that returns totals for everything vs. breakdown by user (because the counts don't say anything about userid or fullname in the where clause, no doubt).

但这将返回所有内容的总数与按用户细分的总数(因为计数在where子句中没有提到userid或fullname，毫无疑问)。

I feel like I'm close but missing something. Can anyone point me in the correct direction here? I'm just trying to learn what I'm doing wrong.

我感觉我很亲近，但却错过了一些东西。谁能给我指出正确的方向吗?我只是想知道我做错了什么。

Thank you for your time.

谢谢您的时间。

3 个解决方案

#1

The best way is to use a conditional SUM :

最好的方法是使用条件和:

SELECT CONCAT(B.fname, ' ', B.lname) AS fullname,
       SUM(
           CASE WHEN DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' 
           THEN 1 ELSE 0 END
       ) AS T1,
       SUM(
           CASE WHEN status = 'C' 
                 AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' 
           THEN 1 ELSE 0 END
       ) AS T2,
       SUM(
           CASE WHEN caused_change = 1 
                 AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' 
           THEN 1 ELSE 0 END
       ) AS T3
FROM feedbacks 
    INNER JOIN users B 
        ON feedbacks.userid = B.userid 
GROUP BY fullname

#2

I don't know if you can combine those 3 queries into a single unified query. You've got 3 'where clauses' that overlap:

我不知道你能否将这3个查询合并成一个统一的查询。你有3个重叠的where子句

first query: ALL records that fall in the date range
second query: ALL records that fall in the date range but also have status='C'
third query: ALL records that fall in the date range but also have caused_change=1

第一个查询:所有记录在日期范围的第二个查询:所有记录在日期范围内，但也有状态='C'第三个查询:所有记录在日期范围内，但也有caused_change=1。

In logical terms, the first query already contains all the records from the second and third queries, so you'd be double counting the results from #2 and #3.

从逻辑上讲，第一个查询已经包含了第二个和第三个查询的所有记录，所以您需要加倍计算#2和#3的结果。

However, if you do want to combine all three into a single result, then do the outer query method:

但是，如果您确实想将这三种方法组合为一个结果，那么请执行外部查询方法:

SELECT sum(cnt), fullname
FROM (
    SELECT COUNT(A.id) as cnt, CONCAT(B.fname,' ', B.lname) AS fullname
    FROM feedbacks A
    INNER JOIN users B ON A.userid = B.userid
    WHERE DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11'
    GROUP BY fullname

    UNION

    ...
    WHERE status = 'C' AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11'

    UNION

    ...
    WHERE caused_change = 1 AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11'
) AS innerquery
GROUP BY fullname

Note the alias on the count() in the first inner query. That's to make the counted field appear as 'cnt' for the outer query.

注意第一个内部查询中的count()上的别名。这将使被计数的字段显示为外部查询的“cnt”。

#3

UNION and SUM should do the work. Your code should look like this:

联合和求和应该做这项工作。您的代码应该如下所示:

 select sum(x.col1), x.fullname
from
(SELECT COUNT(A.id)as col1, CONCAT(B.fname,' ', B.lname) AS fullname FROM feedbacks A INNER JOIN users B ON A.userid = B.userid WHERE DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname
UNION
SELECT COUNT(A.id)as col1, CONCAT(B.fname,' ', B.lname) AS fullname FROM feedbacks A INNER JOIN users B ON A.userid = B.userid WHERE status = 'C' AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname
UNION
SELECT COUNT(A.id)as col1, CONCAT(B.fname,' ', B.lname) AS fullname FROM feedbacks A INNER JOIN users B ON A.userid = B.userid WHERE caused_change = 1 AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname
)x
group by x.fullname

#1