php MYSQL 选择查询与 SUM()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8000242/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL Select Query with SUM()
提问by DonCroce
I have the following table:
我有下表：
| 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 |
I need the following output:
我需要以下输出：
xxx > Total: 3 // Clicked: 2 // Viewed 1
yyy > Total: 4 // Clicked: 2 // Viewed 1
I know that I have to use some sort of SUM() in my query, but I don't know how to differ between those multiple unique values in the source_id (something like foreach, idk).
我知道我必须在查询中使用某种 SUM()，但我不知道如何区分 source_id 中的多个唯一值（类似于 foreach、idk）。
How can I get such an output which shows stats from all unique source_ids by using only one query?
我怎样才能得到这样一个输出，它只使用一个查询来显示所有唯一 source_ids 的统计信息？
回答by Marco
Try this:
尝试这个：
SELECT source_id, (SUM(clicked)+SUM(viewed)) AS Total
FROM your_table
GROUP BY source_id
回答by RolandoMySQLDBA
Here is your sample data loaded into a table called 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;
Here is what it contains
这是它包含的内容
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)
Now, here is a good query you need to total and sum by campaign + grand total
现在，这是一个很好的查询，您需要按活动 + 总计进行总计和求和
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;
Here is the output:
这是输出：
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)
Now dress it up with the CONCAT function
现在用 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;
Here is that output
这是输出
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)
Give it a Try !!!
试一试 ！！！
回答by flayto
SELECT source_id, SUM(clicked + viewed) AS 'Total'
FROM your_table
GROUP BY source_id
