Hi, I have a problem with an MySQL statement that is driving me mad, I'm using php to connect to the database. this is it : -
SELECT LINE,SUM(IF(MONTH=2,WB.TEU,0)) AS '2' ,SUM(IF(MONTH=3,WB.TEU,0)) AS '3' ,SUM(IF(MONTH=4,WB.TEU,0)) AS '4' ,SUM(IF(MONTH=5,WB.TEU,0)) AS '5' ,SUM(IF(MONTH=6,WB.TEU,0)) AS '6' ,SUM(IF(MONTH BETWEEN 2 AND 6,WB.TEU,0)) AS '2 TO 6' FROM WB WHERE EAST_PORT='A' AND WEST_PORT='B' GROUP BY LINE what I want to produce with this is this : - LINE 2 3 4 5 6 2 TO 6 A 1335 1118 1260 1395 1276 6384 B 432 469 451 480 555 2387 C 504 554 575 517 764 2914 however what I get is this : - LINE 2 3 4 5 6 2 TO 6 A 1118 1260 1395 1276 6384 6384 B 469 451 480 555 2387 2387 C 554 575 517 764 2914 2914 the last column of figures (2 TO 6) has been replicated under 6 and pushed the values that were in 6 into 5 and so on! I can not see for the life of me why this should or could be happening, especially as the equivalent code in MS Access : - SELECT LINE, Sum(IIf(MONTH=2,TEU,0)) AS ['2'], Sum(IIf(MONTH=3,TEU,0)) AS ['3'], Sum(IIf(MONTH=4,TEU,0)) AS ['4'], Sum(IIf(MONTH=5,TEU,0)) AS ['5'], Sum(IIf(MONTH=6,TEU,0)) AS ['6'], SUM(IIF(MONTH BETWEEN 2 AND 6,TEU,0)) AS 'FEB TO JUNE' , SUM(IIF(MONTH BETWEEN 1 AND 6,TEU,0)) AS 'YTD' FROM DUMP WHERE (((DUMP.[EAST PORT])='HONG KONG') AND ((DUMP.[WEST PORT])='UK')) GROUP BY DUMP.LINE; gives the correct results. If anyone has any idea of what is going on here please let me know, I'm hoping its something simple I'm missing.. Thanks Dan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
