SUM function with subquery?

[TomLeMes]

I have a LOAN table (LOAN) with the following fields:
[ul]pk, loan_no, loan_value, loan_status[/ul]
Loans are of LIVE (L) status until they have been fully paid, at which point their status changes to REPAID (R).

I also have a CASH table (CASH) which holds information about lumps of cash ('Reservations') that are sitting in accounts, waiting to be used to pay (or part-pay) loans at a later date. Reservations that are linked to a loan and are waiting to be used have an ASSIGNED (A) status. Once the reservations have been paid into the loan account their status changes to PAID (P). Information held is as follows:
[ul]pk, fk_loan, res_value, res_status[/ul]

Crucially we can part-pay a loan, transferring some of the Reservations but not others. This means that we can have loans of LIVE status that have several reservations, some of which are ASSIGNED, others of which are PAID. I need to write a select statement that will show all loans (Live and Repaid) and the sum of their *Assigned* reservations.

I started with this:

SELECT LOAN.loan_no, LOAN.loan_value, sum(CASH.res_value) as total_assigned
FROM LOAN 
INNER JOIN CASH on LOAN.pk = CASH.fk_loan
GROUP BY LOAN.loan_no, LOAN.loan_value

But the problem with this is that the sum(CASH.res_value) is summing all res_value records, whereas I want to exclude res_value records that are of PAID status. So then I tried adding a WHERE clause like this:

SELECT LOAN.loan_no, LOAN.loan_value, sum(CASH.res_value) as total_assigned
FROM LOAN 
INNER JOIN CASH on LOAN.pk = CASH.fk_loan
WHERE CASH.res_status = 'A' 
GROUP BY LOAN.loan_no, LOAN.loan_value

This does now give me the desired SUM totals, but unfortunately it also filters out any loans that don't have any Reservations of Assigned (A) status. To be clear, I want to show all loans, regardless of the status of their linked Reservations, I just want to exclude Paid reservations from the sum. I can see why both my examples aren't giving me what I want, but is there a way of doing this?

Tom

 

[bborissov]

SELECT LOAN.loan_no, LOAN.loan_value, sum(IIF(CASH.res_status = 'A',CASH.res_value,0000000000.00)) as total_assigned
FROM LOAN
INNER JOIN CASH on LOAN.pk = CASH.fk_loan
GROUP BY LOAN.loan_no, LOAN.loan_value

Borislav Borissov

 

[TomLeMes]

Hi Borislav,

Thanks for your suggestion. It made me realise that perhaps I posted in the wrong forum. I should've given a bit more context. I'm sending the select string from VFP to SQL using a SQLEXEC command. So I guess essentially it is an SQL question. Sorry about that.

Because when I run it I get a syntax error: [ul][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='[/ul]I don't know if I'm right here, but I did a bit more searching around to see if MS SQL Server allowed SUM(IIF(...)) and it seems that maybe it doesn't? However, I found an alternative syntax that seems to do it:

SELECT LOAN.loan_no, LOAN.loan_value, sum(CASE WHEN CASH.res_status = 'A' then CASH.res_value ELSE 0) as total_assigned
FROM LOAN
INNER JOIN CASH on LOAN.pk = CASH.fk_loan
GROUP BY LOAN.loan_no, LOAN.loan_value

Anyway, thanks for your help and apologies again if didn't explain things properly,

Tom

 

[bborissov]

Yes the CASE do the job I am glad you find it.

Borislav Borissov

 

[mreigler]

I don't know if this would be faster or slower, but you could also do a subquery in the join and sum the res_value there. You'd have to look at the execution plan based on your indexes and data to see if one way has an advantage over the other. One advantage may be selecting only the records from CASH where status is 'A' when that's all you really need anyway (which could take advantage of an index).

SELECT LOAN.loan_no, LOAN.loan_value, COALESCE(TOTALCASH.total_assigned,0) AS total_assigned
FROM LOAN
	LEFT JOIN (Select fk_loan, SUM(res_value) AS total_assigned FROM CASH WHERE CASH.res_Status = 'A'
GROUP BY fk_loan) TOTALCASH ON (LOAN.pk = TOTALCASH.fk_loan)

Mike Reigler
Melange Computer Services, Inc

 

[TomLeMes]

Excellent, thanks Mike! Always good to see an alternative way of doing something, especially if there are potential performance benefits. I'll give it a go, thanks,

Tom