Help With Sub Query w/ Multiple tables

[TBC]

I’m trying to make the follow syntax into a sub query. I really new with making subqueries could someone please take a look at the bottom and help me with this?

Basically I'm trying to make a subquery from this

COLLTRANS@.PAYMENTAMOUNT
COLLTRANS2@.PAYMENTDATE
COLLACCT@.RECNUM


My select statement would look like this

SELECT
SUM(COLLTRANS@.PAYMENTAMOUNT),
COLLTRANS2@.PAYMENTDATE,
COLLACCT@.RECNUM
FROM COLLACCT@
INNER JOIN COLLTRANS@ ON COLLTRANS@.ACCOUNTLINK = COLLACCT@.RECNUM
INNER JOIN COLLTRANS2@ ON COLLTRANS2@.RECNUM = COLLTRANS@.RECNUM
WHERE
COLLTRANS2@.PAYMENTDATE BETWEEN '01/01/16' AND '01/31/16'

Here I was trying to put the sub queries and comparing them

(SELECT COUNT(ACCT.RECNUM) FROM COLLACCT@ AS ACCT WHERE ACCT.CLIENTNUMBER=ClientNumber AND ACCT.DATEOFREFERRAL BETWEEN Start AND End)
(SELECT SUM(TRANS.PAYMENTAMOUNT) FROM COLLTRANS@ AS TRANS WHERE COLLTRANS2@.PAYMENTDATE BETWEEN '01/01/16' AND '01/31/16')

SELECT
COUNT(COLLACCT@.RECNUM),
COLLTRANS@.PAYMENTAMOUNT,
COLLTRANS2@.PAYMENTDATE FROM COLLACCT@
INNER JOIN COLLTRANS@ ON COLLTRANS@.ACCOUNTLINK = COLLACCT@.RECNUM
INNER JOIN COLLTRANS2@ ON COLLTRANS2@.RECNUM = COLLTRANS@.RECNUM

(SELECT COUNT(COLLACCT@.RECNUM), COLLTRANS@.PAYMENTAMOUNT, COLLTRANS2@.PAYMENTDATE FROM COLLACCT@ INNER JOIN COLLTRANS@ ON COLLTRANS@.ACCOUNTLINK = COLLACCT@.RECNUM INNER JOIN COLLTRANS2@ ON COLLTRANS2@.RECNUM = COLLTRANS@.RECNUM WHERE COLLTRANS2@.PAYMENTDATE BETWEEN '01/01/16' AND '01/31/16'))

 

[CJ_London]

if using count/sum/avg in a query, you need to group the fields which aren't summed etc and it is customary to alias the sums

And can't remember offhand about bracketing - think still required for SQL server however SQL will highlight where there are issues like this so perhaps doesn't matter

something like (from your first query) -

 SELECT
SUM(COLLTRANS@.PAYMENTAMOUNT) [COLOR=red]ttlPayment[/COLOR],
COLLTRANS2@.PAYMENTDATE,
COLLACCT@.RECNUM
FROM [COLOR=red](COLLACCT@
INNER JOIN COLLTRANS@ ON COLLTRANS@.ACCOUNTLINK = [/COLOR][COLOR=red]COLLACCT@.RECNUM[/COLOR][COLOR=red])
[/COLOR]INNER JOIN COLLTRANS2@ ON COLLTRANS2@.RECNUM = COLLTRANS@.RECNUM
[COLOR=red]GROUP BY [/COLOR][COLOR=red]COLLTRANS2@.PAYMENTDATE[/COLOR][COLOR=red], COLLACCT@.RECNUM[/COLOR]
 WHERE 
COLLTRANS2@.PAYMENTDATE BETWEEN '01/01/16' AND '01/31/16'