MYSQL Select Query with SUM()

Question

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

How can I get such an output which shows stats from all unique source_ids by using only one query?

Answer 1

Try this:

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

Answer 2

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

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 !!!