MYSQL使用SUM()函数的选择查询

Question

我有下面这个表格：

| campaign_id | source_id | clicked | viewed |
----------------------------------------------
| abc         | xxx       | 0       | 0      |  
| abc         | xxx       | 1       | 0      |
| abc         | xxx       | 1       | 1      | 
| abc         | yyy       | 0       | 0      |    
| abc         | yyy       | 1       | 0      |    
| abc         | yyy       | 1       | 1      |    
| abc         | yyy       | 0       | 0      |

我需要以下输出：

xxx > Total: 3 // Clicked: 2 // Viewed 1
yyy > Total: 4 // Clicked: 2 // Viewed 1

我知道在查询中必须使用SUM()，但我不知道如何区分源ID中的多个唯一值（类似于foreach，我不知道）。如何通过仅使用一个查询获取显示来自所有唯一来源ID的统计信息的输出？

- DonCroce

---

我希望当有人对问题投反对票时，能够在评论中解释原因。这是改进问题的唯一途径。 - Rosamunda

Answer 1

试试这个：

SELECT source_id, (SUM(clicked)+SUM(viewed)) AS Total
FROM your_table
GROUP BY source_id

- Marco

---

我不应该按source_id分组吗？否则谢谢，我会试一下！ - DonCroce

你应该使用 GROUP BY source_Id。我认为他还想要 SELECT source_id, SUM(clicked) + SUM(viewed) as Total。 - Jose Rui Santos

@DonCroce：是的，只是一个打字错误。我已经编辑了我的帖子 :) - Marco

Answer 2

这是您的示例数据加载到名为campaign的表中：

CREATE TABLE campaign
(
    campaign_id VARCHAR(10),
    source_id VARCHAR(10),
    clicked int,
    viewed int
);
INSERT INTO campaign VALUES
('abc','xxx',0,0),
('abc','xxx',1,0),
('abc','xxx',1,1),
('abc','yyy',0,0),
('abc','yyy',1,0),
('abc','yyy',1,1),
('abc','yyy',0,0);
SELECT * FROM campaign;

这是它包含的内容。

mysql> DROP TABLE IF EXISTS campaign;
CREATE TABLE campaign
(
    campaign_id VARCHAR(10),
    source_id VARCHAR(10),
    clicked int,
    viewed int
);
INSERT INTO campaign VALUES
('abc','xxx',0,0),
('abc','xxx',1,0),
('abc','xxx',1,1),
('abc','yyy',0,0),
('abc','yyy',1,0),
('abc','yyy',1,1),
('abc','yyy',0,0);
SELECT * FROM campaign;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE campaign
    -> (
    ->     campaign_id VARCHAR(10),
    ->     source_id VARCHAR(10),
    ->     clicked int,
    ->     viewed int
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO campaign VALUES
    -> ('abc','xxx',0,0),
    -> ('abc','xxx',1,0),
    -> ('abc','xxx',1,1),
    -> ('abc','yyy',0,0),
    -> ('abc','yyy',1,0),
    -> ('abc','yyy',1,1),
    -> ('abc','yyy',0,0);
Query OK, 7 rows affected (0.07 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM campaign;
+-------------+-----------+---------+--------+
| campaign_id | source_id | clicked | viewed |
+-------------+-----------+---------+--------+
| abc         | xxx       |       0 |      0 |
| abc         | xxx       |       1 |      0 |
| abc         | xxx       |       1 |      1 |
| abc         | yyy       |       0 |      0 |
| abc         | yyy       |       1 |      0 |
| abc         | yyy       |       1 |      1 |
| abc         | yyy       |       0 |      0 |
+-------------+-----------+---------+--------+
7 rows in set (0.00 sec)

现在，这是一个很好的查询，您需要按广告系列+总计进行汇总和求和。

SELECT
    campaign_id,
    source_id,
    count(source_id) total,
    SUM(clicked) sum_clicked,
    SUM(viewed) sum_viewed
FROM campaign
GROUP BY campaign_id,source_id
WITH ROLLUP;

以下是输出结果：

mysql> SELECT
    ->     campaign_id,
    ->     source_id,
    ->     count(source_id) total,
    ->     SUM(clicked) sum_clicked,
    ->     SUM(viewed) sum_viewed
    -> FROM campaign
    -> GROUP BY campaign_id,source_id
    -> WITH ROLLUP;
+-------------+-----------+-------+-------------+------------+
| campaign_id | source_id | total | sum_clicked | sum_viewed |
+-------------+-----------+-------+-------------+------------+
| abc         | xxx       |     3 |           2 |          1 |
| abc         | yyy       |     4 |           2 |          1 |
| abc         | NULL      |     7 |           4 |          2 |
| NULL        | NULL      |     7 |           4 |          2 |
+-------------+-----------+-------+-------------+------------+
4 rows in set (0.00 sec)

现在使用 CONCAT 函数来美化它。

SELECT
CONCAT(
    'Campaign ',campaign_id,
    ' Source ',source_id,
    ' > Total: ',
    total,
    ' // Clicked: ',
    sum_clicked
    ,' // Viewed: ',
    sum_viewed) "Campaign Report"
FROM
(SELECT
    campaign_id,
    source_id,
    count(source_id) total,
    SUM(clicked) sum_clicked,
    SUM(viewed) sum_viewed
FROM campaign
GROUP BY
campaign_id,source_id) A;

这是输出结果。

mysql> SELECT
    -> CONCAT(
    ->     'Campaign ',campaign_id,
    ->     ' Source ',source_id,
    ->     ' > Total: ',
    ->     total,
    ->     ' // Clicked: ',
    ->     sum_clicked
    ->     ,' // Viewed: ',
    ->     sum_viewed) "Campaign Report"
    -> FROM
    -> (SELECT
    ->     campaign_id,
    ->     source_id,
    ->     count(source_id) total,
    ->     SUM(clicked) sum_clicked,
    ->     SUM(viewed) sum_viewed
    -> FROM campaign
    -> GROUP BY
    -> campaign_id,source_id) A;
+---------------------------------------------------------------+
| Campaign Report                                               |
+---------------------------------------------------------------+
| Campaign abc Source xxx > Total: 3 // Clicked: 2 // Viewed: 1 |
| Campaign abc Source yyy > Total: 4 // Clicked: 2 // Viewed: 1 |
+---------------------------------------------------------------+
2 rows in set (0.00 sec)

试试看！

- RolandoMySQLDBA

Answer 3

SELECT source_id, SUM(clicked + viewed) AS 'Total'
FROM your_table
GROUP BY source_id

- bv8z