Hi guys, I'm working in a telecom company that has Oracle for the call statistics. Now we export the daily stats to a remote mySql.
The daily resume table looks like this: +------------+-----------+-----------------+--------+----------------------+ ----------+--------+--------+ | IMRFLL | IMR906 | IMRTER | IMRTAR | IMRDUR | IMRFAC | IMRCLI | IMRCLL | +------------+-----------+-----------------+--------+----------------------+ ----------+--------+--------+ | 2004-06-01 | 803xxxxxx | xxxxxxxxx | N | 446.916666666666572 | 40355904 | 21 | 26 | | 2004-06-01 | 803xxxxxx | 0 | R | 9.416666666666664 | 40355904 | 21 | 10 | +------------+-----------+-----------------+--------+----------------------+ ----------+--------+--------+ What I need it's to get a report that joins the table to itself two times to get the Normal tarif ( IMRTAR=N) and the Reduced tarif (IMRTAR=R). In Oracle is done using Outer joins like this: SELECT IMRTAB.IMR906 AS NUM906,IMRTAB.IMRFLL AS FLL, SUM(IMRTAB.IMRCLL) AS CLL ,ROUND(SUM(IMRTAB.IMRDSC),2) AS DUR, ROUND(SUM(IMRTAB.IMRDSC)/SUM(IMRTAB.IMRCLL),2) AS PRO, SUM(IMRTAB1.IMRCLL) AS CLL_N,ROUND(SUM(IMRTAB1.IMRDSC),2) AS DUR_N,ROUND(SUM(IMRTAB1.IMRDSC)/SUM(IMRTAB1.IMRCLL),2) AS PRO_N, SUM(IMRTAB2.IMRCLL) AS CLL_R,ROUND(SUM(IMRTAB2.IMRDSC),2) AS DUR_R,ROUND(SUM(IMRTAB2.IMRDSC)/SUM(IMRTAB2.IMRCLL),2) AS PRO_R FROM IMRTAB, IMRTAB IMRTAB1, IMRTAB IMRTAB2 /* here does the tables alias */ WHERE IMRTAB.IMRIDE = IMRTAB1.IMRIDE (+) AND IMRTAB.IMRIDE = IMRTAB2.IMRIDE (+) /* links by the uniqID both alias */ AND (IMRTAB1.IMRTAR (+) = 'N') /* takes N calls and discard the rest for IMRTAB1 */ AND (IMRTAB2.IMRTAR (+) = 'R') /* takes R calls and discard the rest for IMRTAB2 */ AND (IMRTAB.IMRFLL BETWEEN '01/09/2004' and '10/09/2004') AND (IMRTAB.IMRCLI =2584 AND (IMRTAB.IMR906=803xxxxxx )) GROUP BY IMRTAB.IMR906,IMRTAB.IMRFLL ORDER BY IMRTAB.IMR906,IMRTAB.IMRFLL And this query returns something like this: +-----------+------------+--------+--------+--------+--------+ | NUM906 | FLL | CLL | DUR | DUR_N | CLL_N | DUR_R | CLL_R +-----------+------------+--------+--------+--------+--------+ | 803xxxxxx | 2004-09-02 | 1 | 4.30 | 4.30 | 1 | | 803xxxxxx | 2004-09-01 | 2 | 0.00 | 0.00 | 2 | +-----------+------------+--------+--------+--------+--------+ I took out some fields in this graphic just to simplify. I've been trying to get this query running for mysql but I can get the same results and I kill the DB. Does someone knows if I can get the same result ? Thanks is advance, MARTIN [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
