How to get Max of sum of two columns, with associated column - SQL Live

Question

CREATE TABLE emp
(
   empno      NUMBER (4, 0),
   ename      VARCHAR2 (10),
   job        VARCHAR2 (9),
   mgr        NUMBER (4, 0),
   hiredate   DATE,
   sal        NUMBER (7, 2),
   comm       NUMBER (7, 2),
   deptno     NUMBER (2, 0),
   CONSTRAINT pk_emp PRIMARY KEY (empno)
);

This is the table creation, obviously

enter image description here This is the table output

Now what i need to do is find the sum of Sal + Comm for the Salesman rows, and then find the max of those sums, then display that max plus the corresponding ename

Here is my code

     SELECT ename, MAX (SUM (sal + comm)) max_sal
    FROM emp
   WHERE job = 'SALESMAN'
GROUP BY ename

I know this nowhere near correct, but I'm brand new to SQL so I'm very stuck (using SQL live btw)

This is very close to being a duplicate of https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column — MT0, Oct 11 '18 at 07:07

score 0 · Answer 1 · answered Oct 11 '18 at 06:16

0

First you have to generate the Sum of two fields as subquery then you can get the MAX from your subQuery. please us the below query:

select oldQuery.ename,MAX(oldQuery.Max_Sal)

from (select ename, sum(sal+comm) Max_Sal

from emp

where job='SALESMAN'

group by ename) as oldQuery

group by oldQuery.ename

answered Oct 11 '18 at 06:16

G.Nader

847
7
9

Hmm, same as the other suggestion, it is giving me an error saying the command is not properly ended I'm using Oracle Live SQL btw – Gold Pony Boy Oct 11 '18 at 06:19

Jacob · Accepted Answer · 2018-10-11T07:19:57.687

0

Try as

 SELECT deptno,
         MAX (ename) KEEP (DENSE_RANK LAST ORDER BY sal) ename,
         MAX (sal + comm)
    FROM emp
   WHERE job = 'SALESMAN'
GROUP BY deptno

DENSE_RANK - will aggregate over only those rows with the MAXIMUM first

KEEP will use the MAX function only over these results and not the complete results of your query

Edit

Without dept_no

SELECT MAX (ename) KEEP (DENSE_RANK LAST ORDER BY sal) ename,
       MAX (sal + comm)
  FROM scott.emp
 WHERE job = 'SALESMAN'

edited Oct 11 '18 at 07:19

answered Oct 11 '18 at 06:26

Jacob

14,463
65
207
320

This works, but I'm not familiar with some of the code. For instance, what is this line doing: MAX (ename) KEEP (DENSE_RANK LAST ORDER BY sal) ename and that scott.emp is confusing me as well – Gold Pony Boy Oct 11 '18 at 06:30
@GoldPonyBoy More examples are [here](https://oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions) – Jacob Oct 11 '18 at 06:40
Okay, I think I understand, but why did you use the line "FROM scott.emp"? Scott is just a random name form the table, so I'm confused Also, my upvote doesn't change the total as I'm still too new to the site – Gold Pony Boy Oct 11 '18 at 06:42
@GoldPonyBoy `scott` is the schema name. I have removed the `scott` from the query. – Jacob Oct 11 '18 at 06:45
This groups by `deptno` the OP's question has no requirement for this. – MT0 Oct 11 '18 at 07:07
@MT0 I have included a SQL query without `dept_no` – Jacob Oct 11 '18 at 07:10

How to get Max of sum of two columns, with associated column - SQL Live

2 Answers2