MySql select 显示按条件分组的总数

Question

我有这张表:

SALESMAN |   INVOICE     |   VALUE   
1        |   7470        |   10       
1        |   7471        |   20       
1        |   7472        |   30     
2        |   7473        |   40       
2        |   7474        |   50       

我想要一个查询以获得此结果:

SALESMAN |   INVOICE     |   VALUE   |  TOTAL_VALUE  | TOTAL_ITEMS
1        |   7470        |   10      |  40           | 3
1        |   7471        |   20      |  40           | 3
1        |   7472        |   10      |  40           | 3
2        |   7473        |   40      |  90           | 2
2        |   7474        |   50      |  90           | 2

TOTAL_VALUE 是同一SALESMAN 的所有VALUE 的总和。TOTAL_ITEMS 是具有相同 SALESMAN 的行数。

可以在 MySql 中实现这一点吗？

Answer 1

使用GROUP BY

SELECT s.SALESMAN, s.INVOICE, s.VALUE,
xx.TOTAL_VALUE, xx.TOTAL_ITEMS
FROM sales s JOIN
(SELECT SALESMAN,
    SUM(VALUE) AS TOTAL_VALUE,
    COUNT(value) AS TOTAL_ITEMS
    FROM sales
    GROUP BY SALESMAN) xx ON S.SALESMAN = xx.SALESMAN;