Your original Oracle(R) query (slightly reformatted): 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
My MySQL translation: 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 LEFT JOIN IMRTAB IMRTAB1 ON IMRTAB.IMRIDE = IMRTAB1.IMRIDE AND IMRTAB1.IMRTAR = 'N' /* N calls only for IMRTAB1 */ LEFT JOIN IMRTAB IMRTAB2 ON IMRTAB.IMRIDE = IMRTAB2.IMRIDE AND IMRTAB2.IMRTAR = 'R' /* R calls only for IMRTAB2 */ WHERE IMRTAB.IMRFLL BETWEEN '2004-09-01' and '2004-09-10' AND IMRTAB.IMRCLI=2584 AND IMRTAB.IMR906=803xxxxxx GROUP BY IMRTAB.IMR906,IMRTAB.IMRFLL ORDER BY IMRTAB.IMR906,IMRTAB.IMRFLL You were using the Oracle syntax ", ...(+)" to declare your outer joins. The equivalent MySQL form is "LEFT JOIN... ON ...". http://dev.mysql.com/doc/mysql/en/JOIN.html I also had to reformat the dates in your WHERE clause to be MySQL formatted: '01/09/2004' (dd/mm/yyyy) =>> '2004-09-01' (yyyy-mm-dd) http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html http://dev.mysql.com/doc/mysql/en/DATETIME.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine "martin fasani" <[EMAIL PROTECTED]> wrote on 09/28/2004 07:23:38 AM: > > 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] >