How can I subtract from a running total subquery in my query?

Question

I'm trying to create a case where if there's a previous payment I can deduct it from the selected amount date range.

I've created everything I need except the previous payment amounts. I keep running into subquery errors

SELECT 
   acctnmbr
  ,amount*commission/100
  ,(select amount*commission/100 from transactions where trantype=0001 and tran_dt < @startdate) as Previous_Payments
FROM transactions
  where trantype=0001 and tran_dt between @startdate and @enddate

the Previous_Payments is where I run into subquery errors because I use <

Any help is appreciated.

Please tag your question with the database you are using. Sample data and desired results are also helpful. — Gordon Linoff, Dec 27 '18 at 20:30
I would recommend using a CTE for this rather than a subquery. Nonetheless, the reason why you are getting errors in your subquery is because it's in the projection, therefore it must return a single value. Your subquery as it is will return multiple values. It will also return ALL prior transactions without any constraint other than the trantype and tran_dt, which is probably not what you really want. — Brian Driscoll, Dec 27 '18 at 20:40

score 1 · Accepted Answer · answered Dec 27 '18 at 20:54

The reason why you are getting errors in your subquery is because it's in the projection, therefore it must return a single value. Your subquery as it is will return multiple values. It will also return ALL prior transactions without any constraint other than the trantype and tran_dt, which is probably not what you really want.

I'm also assuming you want an aggregate sum of everything since it seems to make sense based on the little description you've provided. However I'll be happy to update my answer if you provide some additional info as Gordon Linoff suggests.

You can attack this in a number of different ways...

Common Table Expression (CTE):

WITH PriorPayments AS
SELECT acctnmbr, amount*commission/100 as payment from transactions where trantype=0001 and tran_dt < @startdate

SELECT trx.acctnmbr, 
       sum(trx.amount*trx.commission/100) as total_payment, 
       sum(ISNULL(pp.payment,0)) as prior_payment 
FROM transactions trx 
LEFT JOIN PriorPayments pp ON trx.acctnmbr=pp.acctnmbr
WHERE trx.trantype=0001 
AND trx.tran_dt BETWEEN @startdate and @enddate
GROUP BY trx.acctnmbr

Subquery:

SELECT trx.acctnmbr, 
       sum(trx.amount*trx.commission/100) as total_payment, 
       sum(ISNULL(pp.payment,0)) as prior_payment 
FROM transactions trx 
LEFT JOIN (
    SELECT acctnmbr, amount*commission/100 as payment from transactions where trantype=0001 and tran_dt < @startdate
) AS pp ON trx.acctnmbr=pp.acctnmbr
WHERE trx.trantype=0001 
AND trx.tran_dt BETWEEN @startdate and @enddate
GROUP BY trx.acctnmbr

Thank you, it's exactly what I needed. – Nick Dec 27 '18 at 21:04 — Nick, Dec 27 '18 at 21:04

How can I subtract from a running total subquery in my query?

1 Answers1

Common Table Expression (CTE):

Subquery: