SQL Query returning duplicate records

Question

SQL Query returning duplicate records

Navigation

#1 by (2 votes)

1

I have a BIG problem in a SELECT:

I have two Tables' adwords_performance 'and' analytics_transaction 'and I need to do a SELECT by adding columns' adwords_performance.clicks ', '< strong> adwords_performance.impressions 'and' analytics_transaction.transaction_revenue '. So quiet, but when I do SELECT it returns me wrong values at 'adwords_performance.clicks', 'adwords_performance.impressions'.

Thanks in advance for your attention and help!

Query

SELECT
adwords_performance.date,
Sum(adwords_performance.clicks) AS clicks,
Sum(adwords_performance.impressions) AS impressions,
Sum(analytics_transaction.transaction_revenue) AS revenue
FROM
adwords_performance
LEFT JOIN analytics_transaction 
ON analytics_transaction.date = adwords_performance.date AND
analytics_transaction.adwords_id = adwords_performance.adwords_id AND
analytics_transaction.adwords_campaign_id = adwords_performance.adwords_campaign_id
WHERE
adwords_performance.date = '2015-03-01' AND
adwords_performance.adwords_id = '0123456789'
GROUP BY
adwords_performance.date

Result

+------------+--------+-------------+---------+
| date       | clicks | impressions | revenue |
+------------+--------+-------------+---------+
| 2015-03-01 |    465 |       21017 | 1937.71 |
+------------+--------+-------------+---------+

Result that was to be returned

+------------+--------+-------------+---------+
| date       | clicks | impressions | revenue |
+------------+--------+-------------+---------+
| 2015-03-01 |    364 |       18577 | 1937.71 |
+------------+--------+-------------+---------+

Tables

mysql> describe adwords_performance;
+--------------------------------+---------------------------------+------+-----+---------+----------------+
| Field                          | Type                            | Null | Key | Default | Extra          |
+--------------------------------+---------------------------------+------+-----+---------+----------------+
| id                             | int(20) unsigned                | NO   | PRI | NULL    | auto_increment |
| adwords_id                     | bigint(255) unsigned            | NO   |     | NULL    |                |
| adwords_campaign_id            | bigint(255) unsigned            | NO   |     | NULL    |                |
| campaign_network_type          | varchar(255)                    | NO   |     | NULL    |                |
| date                           | date                            | NO   |     | NULL    |                |
| week                           | date                            | NO   |     | NULL    |                |
| week_day                       | varchar(25)                     | NO   |     | NULL    |                |
| clicks                         | int(255)                        | NO   |     | NULL    |                |
| impressions                    | int(255)                        | NO   |     | NULL    |                |
| cost                           | int(255)                        | NO   |     | NULL    |                |
| av_position                    | decimal(2,1)                    | NO   |     | NULL    |                |
| clicks_converted               | int(255)                        | NO   |     | NULL    |                |
| estimated_converted            | int(255)                        | NO   |     | NULL    |                |
| estimated_converted_rate       | decimal(10,2) unsigned zerofill | NO   |     | NULL    |                |
| clicks_converted_assisted      | int(255)                        | NO   |     | NULL    |                |
| impressions_converted_assisted | int(255)                        | NO   |     | NULL    |                |
| lost_budget                    | varchar(255)                    | NO   |     | NULL    |                |
| clicks_invalid                 | int(255)                        | NO   |     | NULL    |                |
+--------------------------------+---------------------------------+------+-----+---------+----------------+

mysql> describe analytics_transaction;
+----------------------+----------------------+------+-----+---------+----------------+
| Field                | Type                 | Null | Key | Default | Extra          |
+----------------------+----------------------+------+-----+---------+----------------+
| id                   | int(15) unsigned     | NO   | PRI | NULL    | auto_increment |
| analytics_id         | bigint(255) unsigned | NO   | MUL | NULL    |                |
| date                 | date                 | NO   |     | NULL    |                |
| time                 | int(2)               | NO   |     | NULL    |                |
| source               | varchar(255)         | NO   |     | NULL    |                |
| medium               | varchar(255)         | NO   |     | NULL    |                |
| city                 | varchar(255)         | NO   |     | NULL    |                |
| adwords_id           | bigint(255) unsigned | NO   |     | NULL    |                |
| adwords_campaign_id  | bigint(255) unsigned | NO   |     | NULL    |                |
| transaction_id       | bigint(255) unsigned | NO   | MUL | NULL    |                |
| transaction_revenue  | double(10,2)         | NO   |     | NULL    |                |
| transaction_shipping | double(10,2)         | NO   |     | NULL    |                |
| transaction_tax      | double(10,2)         | NO   |     | NULL    |                |
| product_quantity     | int(10)              | NO   |     | NULL    |                |
+----------------------+----------------------+------+-----+---------+----------------+

mysql sql join query select

asked by anonymous 19.03.2015 / 21:16

1 answer

TypeError with calculated field (Django) Best way to display a form inside another form?

score 2 · Answer 1

The problem is that your analytics_transaction table has more than one record per adwords_performance multiplying the clicks. You can resolve this by doing a subquery to get the desired results from this table, eliminating the join. However since all fields must be GROUP BY you should use select based on the result of query previous. The result is as follows:

SELECT x.date,
       SUM(x.clicks) as clicks,
       SUM(x.impressions) as impressions,
       SUM(x.revenue) as revenue
  FROM (SELECT ap.date,
               ap.clicks AS clicks,
               ap.impressions AS impressions,
               (SELECT SUM(at.transaction_revenue)
                 FROM analytics_transaction at
                WHERE at.adwords_id = ap.adwords_id
                  AND at.adwords_campaign_id = ap.adwords_campaign_id
                  AND at.date = at.date) AS revenue
          FROM adwords_performance ap
         WHERE ap.date = '2015-03-01'
           AND ap.adwords_id = '0123456789') x
 GROUP BY x.date