how to get sum of 2 different cloumns from 2 table based on PK in SQL

moayyad

SOLVED CLOSED ACTIVE ANSWERED
User: moayyad
Posted: on Jun 08, 2016 08:07 AM
Forum: SQL Server
Answer: 1
Views: 2580

hi all

i want code to get sum of 2 columns based on PK like

first table (Doctors)

Code_pk Doctor_name

1 Ahmad

second table (doctor_payments)

code_Fk payment

1 100

1 200

third table (Doctor_amounts )

Code_fk amount

1 50

1 300

i want to show in grideview

=========================

name sum(payments) sum(amounts)

ahmad 300 350

** i write sql query but its show first sum correct and second sum not correct

=======================================

SELECT doctors.doctor_name, SUM(Doctor_Amounts.amount ) AS Expr2, SUM(Doctor_payments.payment) AS Expr1 FROM doctors LEFT OUTER JOIN Doctor_payments ON

doctors.code_pk = Doctors_payments.code_fk

LEFT OUTER JOIN Doctor_amounts ON doctors.code = Doctor_amounts.code_fk GROUP BY doctors.doctor_name

================

Download FREE API for Word, Excel and PDF in ASP.Net

Shashika...

0 1

Reply
ANSWER
User: Shashikant
Replied: on Jun 08, 2016 08:08 AM
Modified: on Jun 08, 2016 08:08 AM
Report

Please refer below code.

SQL

DECLARE @Doctors TABLE(Code_pk INT,Doctor_name VARCHAR(50))

INSERT INTO @Doctors VALUES(1,'Ahmad')

DECLARE @doctor_payments TABLE(code_Fk INT,payment INT)
INSERT INTO @doctor_payments VALUES(1,100)
INSERT INTO @doctor_payments VALUES(1,200)


DECLARE @Doctor_amounts TABLE(code_Fk INT,payment INT)
INSERT INTO @Doctor_amounts VALUES(1,50)
INSERT INTO @Doctor_amounts VALUES(1,300)

SELECT d.Doctor_name
,(SELECT SUM(payment) FROM @doctor_payments WHERE code_Fk = d.Code_pk ) as 'sum(payments)'
,(SELECT SUM(payment) FROM @Doctor_amounts WHERE code_Fk = d.Code_pk ) as 'sum(amounts)' 
FROM @Doctors d

Output

Doctor_name sum(payments) sum(amounts)

Ahmad 300 350

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

how to get sum of 2 different cloumns from 2 table based on PK in SQL

Company

Explore

Follow us

Contact Us