CODEWITHSUNDEEP
sql
zairahCS
zairahCS

Reputation: 325

Error 1241 in sql

I am trying to make an sql query to subtract two values from different table to determine the remaining balance of each client. here's my sql query. but i get an error 1241 (operand should contain 1 column(s)). i already put an alias, "BALANCE". 

SELECT tblClientInfo.ClientID, 
  (SELECT tblLoanPayment.ClientID,  
     SUM(tblLoanPayment.AmountPaid) FROM tblLoanPayment) -
  (SELECT tblLedger.ClientID,  tblLedger.LoanAmount from tblLedger) AS BALANCE
FROM tblClientInfo, tblLedger, tblLoanPayment 
WHERE tblClientInfo.ClientID = 1

Can you help me? Thanks in Advance.

Upvotes: 1

Views: 577

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

Your second subselect is giving two columns and - only operates on single values.

Your subselects may also give strange results if no payments have been made; I find the query easier to read if you just rewrite it as a JOIN.

SELECT ci.ClientID, ledger.LoanAmount - COALESCE(SUM(AmountPaid), 0) Remaining
FROM tblClientInfo ci 
JOIN tblLedger ledger ON ci.ClientID=ledger.ClientId 
LEFT JOIN tblLoanPayment lp ON ci.ClientID=lp.ClientId
GROUP BY ci.ClientID, ledger.loanAmount

Demo here.

Upvotes: 0

gdoron
gdoron

Reputation: 150313

You're seleting two columns!

(Select tblLoanPayment.ClientID,  sum(tblLoanPayment.AmountPaid)

You must select one column only to use math operators!

SELECT  
  (SELECT SUM(t.AmountPaid)
   FROM tblLoanPayment t
   WHERE t.ClientID = 1) -
  (SELECT t.LoanAmount 
   from tblLedger
   WHERE t.ClientID = 1)
FROM dual;

Upvotes: 3

Related Questions