How to write the select query between two tables using sum method and where condition

Question

5.00/5 (1 vote)

See more:

I have two tables names and data in that tables are given below

Loans_Advances_Master:

Dept_id     emp_Id   loan_approved_amt    loan_adv_no
------------------------------------------------------
De_1        emp_1     10000                LA_1
de_1        emp_1     10000                LA_2

loans_deduction_details:

dept_Id   EMp_Id    Loan_deduction_amt  Loan_adv_no
-------------------------------------------------
de_1       emp_1       5000                LA_1
de_1       emp_1       5000                LA_1
de_1       Emp_1       5000                LA_2

how to write select query for above two tables for pending laon i.e
loan_approved_amt for loan_adv_no is LA_1 equl to sum(loan_deduction_amt) from loans_deduction_details form Loan_adv_no Then show 0 other wise show amount required amount should he pay..

How to get the output like This for EMp_1

de_1  emp_1    0
de_1   emp_1   5000

de

What I have tried:

SQL

select Dept_id, Emp_id, Loan_Adv_No,((select loan_adv_no,sum(Loan_Approved_Amont) from [dt_agrovet].[dbo].[Loans_Advances_Master]  group by Loan_Adv_No)- (select sum(loan_deduction_amount) from loans_deduction_details  group by emp_id,Loan_Adv_No) ) as loanamount  from [dt_agrovet].[dbo].[Loans_Advances_Master]   where emp_Id='emp_1'

can please any one give me the solution for this ...
Thank you ...

Posted 24-Apr-18 19:49pm

Member 13723018

Updated 25-Apr-18 0:22am

Maciej Los

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santosh kumar Pithani · Answer 1 · 2018-04-24T20:57:00

SQL

SELECT LAM.Dept_id
      ,LAM.emp_Id
      ,(LAM.loan_adv_no-(
	         SELECT SUM(LDD.Loan_deduction_amt)
	           FROM  loans_deduction_details AS LDD
			    WHERE LDD.Loan_adv_no=LAM.loan_adv_no AND LDD.Emp_Id=LAM.Emp_Id)
				    ) AS BalanceAmount
  FROM Loans_Advances_Master AS LAM

GKP1992 · Answer 2 · 2018-04-25T00:23:00

I made some changes in the table structure to make the query cleaner. I removed the columns "emp_id", "dept_id", and "loan_adv_no" from the table "loans_deduction_details" and added a column to hold the id of the good row of "Loans_Advances_Master" to act as a foreign key.

Here is my what my fiddle looks like.

SQL

create table LAM (id int, depId varchar(20), empId varchar(20), loanId varchar(20), loanAmt int);
create table LDD(id int, lamId int, loadDAmt int);

insert into LAM values (1,'De_1','emp_1', 'LA_1', 10000), (2,'De_1','emp_1', 'LA_2', 10000);
insert into LDD values (1,1,5000),(2,1,5000),(3,2,5000);

Now that the tables are initialized I just need to query them.

SQL

select temp.depID, temp.empID, temp.loanId, temp.loadAmt
  from (
    select 
    LAM.depID as depID, 
    LAM.empID as empID, 
    LAM.loanId as loanId, 
    LAM.loanAmt - SUM(LDD.loadDAmt) AS loadAmt 
    FROM LDD 
    INNER JOIN LAM ON LDD.lamId = LAM.Id
    GROUP BY LDD.lamId
  ) temp;

You can also check the fiddle here [^].

How to write the select query between two tables using sum method and where condition

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0