MS SQL Query Sum of subquery

Question

I need a help i getting following output from the query .

SELECT
      ARG_CONSUMER,
      cast(ARG_TOTALAMT as float)/100 AS 'Total',
      (SELECT SUM(cast(DAMT as float))/100 FROM DEBT WHERE DDATE >= ARG.ARG_ORIGDATE AND DDATE <= ARG.ARG_LASTPAYDATE AND DTYPE IN ('CSH','CNTP','DDR','NBP') AND DCONSUMER = ARG.ARG_CONSUMER
        ) AS 'Paid'
FROM
     ARGMASTER ARG
WHERE ARG_STATUS = '1'

Current output is a list of all records...

But what i want to achieve here is

count of arg consumers
Total of ARG_TOTALAMT
total of that subquery  PAID
difference between PAID & Total amount.

I am able to achieve first two i.e. count of consumers & total of ARG _ TOTALAMT... but i am confused about sum of of ...i.e.

sum (SELECT SUM(cast(DAMT as float))/100 FROM DEBT WHERE DDATE >= ARG.ARG_ORIGDATE AND DDATE <= ARG.ARG_LASTPAYDATE AND DTYPE IN ('CSH','CNTP','DDR','NBP') AND DCONSUMER = ARG.ARG_CONSUMER) AS 'Paid'

Please advice

Answer 1

Changing the subquery to an OUTER APPLY

SELECT
      COUNT(DISTINCT ARG_CONSUMER) AS [count of arg consumers],
      cast(ARG_TOTALAMT as float)/100 AS [Total],
      foo.Paid,
      cast(ARG_TOTALAMT as float)/100 - foo.Paid AS [difference between PAID & Total amount]
FROM
     ARGMASTER ARG
     OUTER APPLY
     (
     SELECT
         SUM(cast(D.DAMT as float))/100 AS Paid
     FROM DEBT D
     WHERE
          DDATE >= ARG.ARG_ORIGDATE AND D.DDATE <= ARG.ARG_LASTPAYDATE
          ANDD. DTYPE IN ('CSH','CNTP','DDR','NBP') AND D.DCONSUMER = ARG.ARG_CONSUMER
     ) foo
WHERE ARG.ARG_STATUS = '1'