Hi All,

I have a problem.  I have 2 tables:

mysql> SELECT int_traffic.day, sum(int_traffic.deliveries) as deliveries
    -> FROM int_traffic
    -> WHERE int_traffic.day between '2004-05-01' and '2004-05-31'
    -> GROUP BY int_traffic.day
    -> ORDER BY int_traffic.day;
+------------+------------+
| day        | deliveries |
+------------+------------+
| 2004-05-01 |          6 |
| 2004-05-03 |         40 |
| 2004-05-04 |        102 |
| 2004-05-05 |        103 |
| 2004-05-06 |        119 |
| 2004-05-07 |         57 |
| 2004-05-08 |         17 |
| 2004-05-09 |         12 |
| 2004-05-10 |         87 |
| 2004-05-11 |         82 |
| 2004-05-12 |         86 |
| 2004-05-13 |         76 |
| 2004-05-14 |         68 |
| 2004-05-15 |         38 |
| 2004-05-16 |          9 |
| 2004-05-17 |        126 |
| 2004-05-18 |         83 |
| 2004-05-19 |        149 |
| 2004-05-20 |         57 |
| 2004-05-21 |         76 |
| 2004-05-22 |         18 |
| 2004-05-23 |          7 |
| 2004-05-24 |         99 |
| 2004-05-25 |         78 |
| 2004-05-26 |         56 |
| 2004-05-27 |         16 |
| 2004-05-28 |          8 |
+------------+------------+

And:

mysql> SELECT daily_traffic.day, sum(daily_traffic.deliveries) as
deliveries
    -> FROM daily_traffic
    -> WHERE daily_traffic.day between '2004-05-01' and '2004-05-31'
    -> GROUP BY daily_traffic.day
    -> ORDER BY daily_traffic.day;
+------------+------------+
| day        | deliveries |
+------------+------------+
| 2004-05-01 |     101936 |
| 2004-05-03 |      96954 |
| 2004-05-04 |     190871 |
| 2004-05-05 |     192995 |
| 2004-05-06 |     243518 |
| 2004-05-07 |     353032 |
| 2004-05-08 |     197238 |
| 2004-05-09 |      92295 |
| 2004-05-10 |     208991 |
| 2004-05-11 |     201924 |
| 2004-05-12 |     206812 |
| 2004-05-13 |     200308 |
| 2004-05-14 |     184485 |
| 2004-05-15 |     100602 |
| 2004-05-16 |      89567 |
| 2004-05-17 |     202828 |
| 2004-05-18 |     196956 |
| 2004-05-19 |     197561 |
| 2004-05-20 |     197962 |
| 2004-05-21 |     194170 |
| 2004-05-22 |     102406 |
| 2004-05-23 |      86801 |
| 2004-05-24 |     201442 |
| 2004-05-25 |     197852 |
| 2004-05-26 |     191453 |
| 2004-05-27 |     185538 |
| 2004-05-28 |     167304 |
| 2004-05-29 |      95079 |
| 2004-05-30 |      80982 |
| 2004-05-31 |      95863 |
+------------+------------+

But when I do this:

mysql> SELECT distinct daily_traffic.day,
    -> sum(daily_traffic.deliveries) as external,
sum(yell_int_traffic.deliveries) as deliveries
    -> FROM daily_traffic
    -> LEFT JOIN yell_int_traffic
    -> ON daily_traffic.day = yell_int_traffic.day
    -> WHERE daily_traffic.day BETWEEN '2004-05-01' AND '2004-05-31' and
daily_traffic.client in ('yell','yell2002') and daily_traffic.type =
'inline'
    -> GROUP by daily_traffic.day;
+------------+----------+------------+
| day        | external | deliveries |
+------------+----------+------------+
| 2004-05-01 |   203872 |         12 |
| 2004-05-03 |   193908 |         80 |
| 2004-05-04 |   954355 |        204 |
| 2004-05-05 |   771980 |        206 |
| 2004-05-06 |   974072 |        238 |
| 2004-05-07 |  1412128 |        114 |
| 2004-05-08 |   394476 |         34 |
| 2004-05-09 |   184590 |         24 |
| 2004-05-10 |   417982 |        174 |
| 2004-05-11 |   403848 |        164 |
| 2004-05-12 |   413624 |        172 |
| 2004-05-13 |   400616 |        152 |
| 2004-05-14 |   737940 |        136 |
| 2004-05-15 |   201204 |         76 |
| 2004-05-16 |   179134 |         18 |
| 2004-05-17 |   811312 |        252 |
| 2004-05-18 |   393912 |        166 |
| 2004-05-19 |   395122 |        298 |
| 2004-05-20 |   791848 |        114 |
| 2004-05-21 |   776680 |        152 |
| 2004-05-22 |   204812 |         36 |
| 2004-05-23 |   173602 |         14 |
| 2004-05-24 |   402884 |        198 |
| 2004-05-25 |  1187112 |        156 |
| 2004-05-26 |   382906 |        112 |
| 2004-05-27 |   742152 |         32 |
| 2004-05-28 |   334608 |         16 |
| 2004-05-29 |    95079 |       NULL |
| 2004-05-30 |    80982 |       NULL |
| 2004-05-31 |    95863 |       NULL |
+------------+----------+------------+

All the results double for the days where there is data in both tables;
e.g. for the 29th, the total goes from 167304 to 334608 in the left
join.  I want to see the same result set format but without the doubled
up numbers.

Does anyone have any ideas how to solve this?

Help will be most appreciated...

Cheers,

Andrew

Sql, query


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to