sql oracle max(sum)

Question

I am trying to write a query in Oracle which will return max value. I have 3 tables Employee, customer , orders.

SELECT a.ID_Employee, a.NAME, a.SURNAME, SUM(c.value) AS "value"  FROM Employee a, CUSTOMER b, ORDERS c WHERE a.ID_Employee = b.ID_Employee and b.ID_CUSTOMER = c.ID_CUSTOMER group by a.ID_Employee, a.NAME, a.SURNAME ;

The result of questions :

id_Employess Name  Surname  Value
7654         KENNETH MARTIN 27348
7555         DANIEL PETERS  11060
7820         PAUL   ROSS    20796,8
7844         MARY   TURNER  58055,9
7560         SARAH  DUNCAN  36191,2
7521         CYNTHIA WARD   9984,2
7499         KEVIN  ALLEN   7870,8
7789         LIVIA  WEST    32274,2
7557         KAREN  SHAW    33655,65

And i need max value from this result should be :

id_Employess Name  Surname  Value
7844         MARY   TURNER  58055,9

score 1 · Accepted Answer · answered Jun 30 '16 at 19:07

1

just use rownum after order by

  select * from (
        select ID_Employee, NAME, SURNAME, value, rownum rn
         from (
        SELECT a.ID_Employee, a.NAME, a.SURNAME, SUM(c.value) AS "value"  
          FROM Employee a, CUSTOMER b, ORDERS c 
         WHERE a.ID_Employee = b.ID_Employee and b.ID_CUSTOMER = c.ID_CUSTOMER  
         group by a.ID_Employee, a.NAME, a.SURNAME 
         order by 4 desc
        )
    ) where rn = 1

answered Jun 30 '16 at 19:07

Анатолий Предеин

551
5
9

I have 1 ask about your answer why < order by 4 > ? – Igorles Jun 30 '16 at 20:05
`ORDER BY 4` means order by the fourth column in the select clause. So it is the same as `ORDER BY value`. – MT0 Jun 30 '16 at 20:24

score 0 · Answer 2 · answered Jun 30 '16 at 19:06

Could be this query

select *  
from 
        (SELECT 
        a.ID_Employee
        , a.NAME
        , a.SURNAME
        , SUM(c.value) AS "value"  
        FROM Employee a, CUSTOMER b, ORDERS c 
        WHERE a.ID_Employee = b.ID_Employee 
        and b.ID_CUSTOMER = c.ID_CUSTOMER 
        group by a.ID_Employee, a.NAME, a.SURNAME ) 
 where  value  =   (SELECT  SUM(c.value)  
        FROM Employee a, CUSTOMER b, ORDERS c 
        WHERE a.ID_Employee = b.ID_Employee 
        and b.ID_CUSTOMER = c.ID_CUSTOMER 
        group by a.ID_Employee, a.NAME, a.SURNAME );

sql oracle max(sum)

2 Answers2