You have to look at the intermediate results of this to understand why your
sums are doubling up:
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'
I suspect that you are getting twice the number of rows than you expected.
Change your statement so that it runs without the GROUP BY and so that it
doesn't SUM() all of the columns. I'll bet you will not see the data
pattern you thought you had.
Respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
The
"Andrew
Braithwaite" To: <[EMAIL PROTECTED]>
<[EMAIL PROTECTED] cc: "Rob Leney" <[EMAIL
PROTECTED]>
com> Fax to:
Subject: problem with totals doubling
when using a right join....
06/02/2004 09:26
AM
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
