Convert SQL server query to Oracle 10g query

based on the calculation you're doing it looks like you might be looking for the employee with the top profits, not the top salary.

if you're really interested in the top salary, do you still want the profits?

assuming you want employee with top profits, try this...

select * from
(SELECT s.empid employeeid,
       s.ename employeename,
       TO_CHAR(SUM((i.price) * (oi.qty)) - (s.salary), '$99,999.99') AS topprofit
  FROM salespersons s
       LEFT JOIN orders o ON s.empid = o.empid
       LEFT JOIN orderitems oi ON o.orderid = oi.orderid
       LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename, s.salary
ORDER BY SUM(i.price) * (oi.qty) - (s.salary) DESC)
where rownum = 1;

Have a read of this article explaining how the ROWNUM pseudo-column works in Oracle: http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

The gist of it would be something like this:

select * from 
(SELECT S.empid EmployeeID  ,
       S.ename EmployeeName  ,
     to_char(SUM((I.price) * (OI.qty)) - (S.salary),'$99,999.99') AS  TOPProfit  
  FROM SALESPERSONS S
         LEFT JOIN ORDERS O
          ON S.empid = O.empid
         LEFT JOIN ORDERITEMS OI
          ON O.orderid = OI.orderid
         LEFT JOIN INVENTORY I
          ON OI.partid = I.partid

GROUP BY S.empid, S.ename, S.salary
ORDER BY SUM(I.price) * (OI.qty) - (S.salary) DESC)
where ROWNUM = 1

Select allOpen in new window

assuming you want the profits of the employee with top salary try this...


SELECT employeeid, employeename, topprofit
  FROM (SELECT s.empid employeeid,
               s.ename employeename,
               TO_CHAR(
                   SUM((i.price) * (oi.qty)) OVER (PARTITION BY s.empid) - (s.salary),
                   '$99,999.99'
               )
                   AS topprofit,
               ROW_NUMBER() OVER (ORDER BY s.salary DESC) rn
          FROM salespersons s
               LEFT JOIN orders o ON s.empid = o.empid
               LEFT JOIN orderitems oi ON o.orderid = oi.orderid
               LEFT JOIN inventory i ON oi.partid = i.partid)
 WHERE rn = 1

This query is close to get the top profited employee but it gives me the top less profited employee. (so S.salary in the order by close is not working)  

 How can I make    ORDER BY s.salary DESC  to give the top profited employee. I changed s.salary to topprofit but I am getting invalid identifier error when I do that.. That is because topprofit is not a column name.

SELECT employeeid, employeename, topprofit
  FROM (SELECT s.empid employeeid,
               s.ename employeename,
               TO_CHAR(
                   SUM((i.price) * (oi.qty)) OVER (PARTITION BY s.empid) - (s.salary),
                   '$99,999.99'
               )
                   AS topprofit,
               ROW_NUMBER() OVER (ORDER BY s.salary DESC) rn
          FROM salespersons s
               LEFT JOIN orders o ON s.empid = o.empid
               LEFT JOIN orderitems oi ON o.orderid = oi.orderid
               LEFT JOIN inventory i ON oi.partid = i.partid)
 WHERE rn = 1 ;

------------------------------------------------------------------------------------------------------------------

And If I run the query below I am getting    not a GROUP BY expression error.

select * from
(SELECT S.empid EmployeeID  ,
       S.ename EmployeeName  ,
     to_char(SUM((I.price) * (OI.qty)) - (S.salary),'$99,999.99') AS  TOPProfit  
  FROM SALESPERSONS S
         LEFT JOIN ORDERS O
          ON S.empid = O.empid
         LEFT JOIN ORDERITEMS OI
          ON O.orderid = OI.orderid
         LEFT JOIN INVENTORY I
          ON OI.partid = I.partid

GROUP BY S.empid, S.ename, S.salary
ORDER BY SUM(I.price) * (OI.qty) - (S.salary) DESC)
where ROWNUM = 1 ;

sorry, about the group by thing, I copied your query too closely and it had a bug in it
(it had salary in the group by which isn't correct)

select * from
(SELECT s.empid employeeid,
       s.ename employeename,
       TO_CHAR(SUM((i.price) * (oi.qty)) - (s.salary), '$99,999.99') AS topprofit
  FROM salespersons s
       LEFT JOIN orders o ON s.empid = o.empid
       LEFT JOIN orderitems oi ON o.orderid = oi.orderid
       LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename
ORDER BY SUM(i.price) * (oi.qty) - (s.salary) DESC)
where rownum = 1;

After running your modified query, for some reason I am still getting the same  error:

Error starting at line 6 in command:

Line 6.   select * from
(SELECT s.empid employeeid,
       s.ename employeename,
 TO_CHAR((SUM(i.price) * (oi.qty) - (s.salary)), '$99,999.99') AS topprofit
  FROM salespersons s
       LEFT JOIN orders o ON s.empid = o.empid
       LEFT JOIN orderitems oi ON o.orderid = oi.orderid
       LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename
ORDER BY (SUM(i.price) * (oi.qty) - (s.salary)) DESC)
where rownum = 1

Error at Command Line:9 Column:33 - This line is this:  TO_CHAR((SUM(i.price) * (oi.qty) - (s.salary)), '$99,999.99') AS topprofit

Error report:
SQL Error: ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:

---------------------------------------------------------------------------

Also, the query result below is very close to what I need but  if I use  s.salary in  this order statement. ORDER BY s.salary DESC , it gives me the less profited employee instead of top 1 profited  employee.     using topprofit would do it but since it is not an actuall column, I am getting "invalid identifier error" if I replace it with s.salary in the order clause below.

SELECT employeeid, employeename, topprofit
  FROM (SELECT s.empid employeeid,
               s.ename employeename,
               TO_CHAR(
                   SUM((i.price) * (oi.qty)) OVER (PARTITION BY s.empid) - (s.salary),
                   '$99,999.99'
               )
                   AS topprofit,
               ROW_NUMBER() OVER (ORDER BY s.salary DESC) rn
          FROM salespersons s
               LEFT JOIN orders o ON s.empid = o.empid
               LEFT JOIN orderitems oi ON o.orderid = oi.orderid
               LEFT JOIN inventory i ON oi.partid = i.partid)
 WHERE rn = 1 ;

the row number query doesn't apply,  I only suggested that because of ambiguity in what you were looking for.  Now that I know you don't really care about the top salary that no longer applies.

yes, only need the mots profitable salesperson.

some of the time you have the qty as part of the sum, sometimes you don't.
which is correct?

depending on what is supposed to be in the sum, you may need both salary and qty in the group by or neither or only one of them

I assume it should be but I'd like to confirm

going back to the beginning...

does this return the correct data? but too much and not sorted?

SELECT   s.empid employeeid, s.ename employeename, SUM(i.price * oi.qty) - s.salary profit
    FROM salespersons s
         LEFT JOIN orders o ON s.empid = o.empid
         LEFT JOIN orderitems oi ON o.orderid = oi.orderid
         LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename, s.salary

qty should be in the sum:

what about the last query I posted, is the data correct?  it's basically just your original query with extra () removed.

I want to confirm the data is correct, otherwise sorting/filtering on it won't help

yes, the query gives all the correct records.  Now, need to show only Top 1(topprofited salesperson)

SELECT   s.empid employeeid, s.ename employeename, SUM(i.price * oi.qty) - s.salary profit
    FROM salespersons s
         LEFT JOIN orders o ON s.empid = o.empid
         LEFT JOIN orderitems oi ON o.orderid = oi.orderid
         LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename, s.salary

thanks.  I modified it little to get the topprofited employee instead of less one. so it is working now. so  how can I modify it using Rank function to get the second most profited employee. (that could be the one that with second positive profit or negative profit)

select * from
(select * from
(SELECT   s.empid employeeid, s.ename employeename, to_char(SUM(i.price * oi.qty) - s.salary,'$99,999.99') profit
    FROM salespersons s
         LEFT JOIN orders o ON s.empid = o.empid
         LEFT JOIN orderitems oi ON o.orderid = oi.orderid
         LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename, s.salary )
order by profit )
where rownum=1 AND PROFIT IS NOT NULL;

you shouldn't put the TO_CHAR on the inner query, that will cause the results to be sorted as text, rather than numerically

I assume you've gotten your answer, you've closed 3 times without asking for more information despite the attempts to open duplicate questions.