Mysql查询，在相同的输出中提供总和和子查询和。

Question

如何创建一个查询，该查询在一列中提供总和，而小计依赖于另一列中的查询？

给出下表

mysql> +------+------+------+--------+
-> | A    | B    | num  | status |
-> +------+------+------+--------+
-> | e    | f    |   34 | Y      |
-> | e    | f    |   45 | Y      |
-> | g    | h    |   22 | N      |
-> | a    | f    |   55 | Y      |
-> | d    | g    |   66 | N      |
-> | e    | f    |   67 | Y      |
-> | a    | f    |   88 | Y      |
-> | e    | g    |   36 | N      |
-> | d    | g    |   44 | N      |
-> | a    | h    |   33 | Y      |
-> | a    | g    |   33 | N      |
-> | e    | x    |  343 | Y      |
-> | a    | a    |   33 | N      |
-> +------+------+------+--------+
-> 13 rows in set (0.00 sec)

我想输出一个表，它显示:按A分组；num的总和num的总和，其中status = Y。

这是：

select A, sum(num),
       (select sum(num) from  test where ( status = 'Y')) 
from test 
group by A;

mysql> +------+----------+----------------------------------------------------+
-> | A    | sum(num) | (select sum(num) from  test where ( status = 'Y')) |
-> +------+----------+----------------------------------------------------+
-> | a    |      242 |                                                665 |
-> | d    |      110 |                                                665 |
-> | e    |      525 |                                                665 |
-> | g    |       22 |                                                665 |
-> +------+----------+----------------------------------------------------+

...provides错误的结果，偏离了方向。它给出了第3栏中所有A的总和。

我本希望：

mysql> +------+----------+----------------------------------------------------+
-> | A    | sum(num) | (select sum(num) from  test where ( status = 'Y')) |
-> +------+----------+----------------------------------------------------+
-> | a    |      242 |                                                176 |
-> | d    |      110 |                                                  0 |
-> | e    |      525 |                                                489 |
-> | g    |       22 |                                                  0 |
-> +------+----------+----------------------------------------------------+

br AK

Answer 1

你可以试试这个：

SELECT 
    `A`,
    SUM(`num`) as `sum_num`,
    SUM(IF(`status` = 'Y',`num`,0)) as `sum_status_yes`
FROM `test`
GROUP BY `A`
ORDER BY `A`;

您也可以使用此技术来获取计数、平均值等。

Answer 2

您可以使用条件聚合：

select a, sum(num),
       sum(case when status = 'Y' then num else 0 end)
from t
group by a;

Answer 3

SELECT A, SUM(num), SUM( IF( status='Y', num, 0 ) ) 
FROM test 
GROUP BY A