mysql - 使用分组依据对不同字段进行计数

Question

我正在尝试这样做

SELECT b.sponso , b.source_id , Count(Distinct b.source_id) As nb_part
FROM buzzes b 
WHERE b.type = 3 AND b.sponso_id != 0
GROUP BY b.source_id , b.sponso 

输出:

+------------+--------------+--------+
| Sponso     | source_id   | nb_part | 
+------------+--------------+--------+
| A          | 954711      | 1       | 
| A          | 587741      | 1       | 
| B          | 321447      | 1       | 
| B          | 350         | 1       |
+------------+--------------+--------+

以及我期望拥有的东西

+------------+--------------+--------+
| Sponso     | source_id   | nb_part | 
+------------+--------------+--------+
| A          | 954711      | 2       | 
| B          | 321447      | 2       | 
+------------+--------------+--------+

我想计算 nb_part 和 Sponso 分组的总和，但我的查询被困住了

Answer 1

您似乎想要每个 sponso 的最大 source_id 和不同 source_id 的数量。 “Per sponso”在 SQL 中翻译为 GROUP BY sponso。

SELECT sponso, MAX(source_id) AS source_id, COUNT(DISTINCT source_id) AS nb_part
FROM buzzes
WHERE type = 3 AND sponso_id != 0
GROUP BY sponso
ORDER BY sponso;

或者也许您想聚合查询:

SELECT sponso, MAX(source_id) AS source_id, SUM(nb_part) AS nb_part
FROM
(
  SELECT b.sponso , b.source_id , Count(Distinct b.source_id) As nb_part
  FROM buzzes b 
  WHERE b.type = 3 AND b.sponso_id != 0
  GROUP BY b.source_id , b.sponso 
)
GROUP BY sponso
ORDER BY sponso;