MYSQL Select Query с SUM ()

У меня есть следующая таблица:

| 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).

Как я могу получить такой вывод, который отображает статистику из всех уникальных исходных файлов, используя только один запрос?

Solutions Collecting From Web of "MYSQL Select Query с SUM ()"

Попробуй это:

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

Вот ваши данные образца, загруженные в таблицу, называемую кампанией:

 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)

Попробуйте !!!

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