MYSQL Select Query with SUM（）

php mysql sql

2022-08-30 19:56:32

我有下表：

| 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（），但我不知道如何区分source_id中的多个唯一值（类似于foreach，idk）。

如何通过仅使用一个查询来获取显示所有唯一source_ids统计信息的输出？

答案 1

试试这个：

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

答案 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)

试一试!!!