CODEWITHSUNDEEP
sqloracle-databasecompare
Mgnfcnt
Mgnfcnt

Reputation: 129

Oracle How To Compare Rows?

I have there table. 

SALESMAN_REGION
(
       SALESMAN_ID    INTEGER,
       REGION_ID      INTEGER
);

SALESMAN
(
       SALESMAN_ID    INTEGER,
       SALESMAN_NAME  VARCHAR2(50)       
);

TABLE SALES
(
       SALE_ID        INTEGER,
       PRODUCT_ID     INTEGER,
       SALESMAN_ID    INTEGER,
       YEAR           INTEGER,
       Quantity       INTEGER,
       PRICE          INTEGER
);

And i need this information Regions which Tony had more sales than Kevin. And i wrote this query 

 select s.region_id,sum(s.quantity),s.salesman_id from   sales s where s.salesman_id in (
select sr.salesman_id from salesman_region sr
inner join
salesman sm on sm.salesman_id = sr.salesman_id
where 
sm.salesman_name = 'Tony' or sm.salesman_name = 'Kevin') group by s.region_id ,s.salesman_id order by s.region_id;

Output is:

REGION_ID   SUM(S.QUANTITY) SALESMAN_ID
1   10          1776             10
2   10          1603             30
3   20          1813             10
4   20          1479             30
5   30          1218             10
6   30          1516             30

But i dont know how to compare this or another way. Can anyone help me?

Upvotes: 1

Views: 237

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I would approach this using conditional aggregation:

select s.region_id,
       sum(case when sm.salesman_name = 'Tony' then s.quantity else 0 end) as QTony,
       sum(case when sm.salesman_name = 'Kevin' then s.quantity else 0 end) as QKevin
from salesman_region sr inner join
     salesman sm
     on sm.salesman_id = sr.salesman_id inner join
     sales s
     on s.sales_man_id = sm.salesman_id
group by s.region_id
having (sum(case when sm.salesman_name = 'Tony' then s.quantity else 0 end) >
        sum(case when sm.salesman_name = 'Kevin' then s.quantity else 0 end) 
       )

Note: for this to work, it assumes that a salesman is in only one region. If this is not true, your data structure cannot answer your question because you don't know which sales were in which regions.

Upvotes: 1

Related Questions