CODEWITHSUNDEEP
sql-server
Soumi
Soumi

Reputation: 63

Removing Subqueries

I have 2 tables tab1 and tab2.

tab1:

 id    name     monthid  salary   inflow
-----------------------------------------
 1     mohan      1       2000     1000
 1     mohan      3       3000     1000
 1     mohan      4       4500     1600
 1     mohan      2       2500     1200

in tab2 I want this output:

id  name   salary  inflow 
--------------------------
1   mohan   12000   1600

In tab2, salary column is the sum of salary of tab1 and inflow is the inflow of highest month.

I tried this query: 

Insert into tab2(id, name, salary)
    select id, name, sum(salary) 
    from tab1

 update tab2 
 set inflow = (select inflow 
               from tab1 
               where monthid = max(monthid))

But I know this is not the correct method.

Can anyone help me to correct this query? And I also want to remove the subqueries.

Upvotes: 1

Views: 88

Answers (2)

SQL006
SQL006

Reputation: 492

DECLARE @tab1 table(id int,name varchar(100),monthid int, salary int,inflow int)

INSERT INTO @tab1

SELECT 1,'Mohan',1,2000,1000

UNION ALL

SELECT 1,'Mohan',3,3000,1000

UNION ALL

SELECT 1,'Mohan',4,4500,1600

UNION ALL

SELECT 1,'Mohan',2,2500,1200




SELECT top 1 
id, name,SUM(salary) OVER(PARTITION BY id) as salary,MAX(inflow) OVER(PARTITION BY id) as inflow
FROM @tab1

OR

SELECT DISTINCT 
id, name,SUM(salary) OVER(PARTITION BY id) as salary,MAX(inflow) OVER(PARTITION BY id) as inflow
FROM @tab1

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

You can use row_number as below

Insert into tab2(id, [name], [salary], inflow) 
   Select id, [name], Salary, inflow from (
      Select id, [name], sum(salary) over(partition by id) as Salary,
           inflow, RowN = Row_number() over (partition by id order by monthid desc) from tab1 ) a
   Where a.RowN = 1

Without subquery you can use top(1) with ties as below

Insert into tab2(id, [name], [salary], inflow)
Select top (1) with ties id, [name], sum(salary) over(partition by id) as salary, inflow
    from tab1 
    order by  Row_number() over (partition by id order by monthid desc)

Upvotes: 1

Related Questions