Do it like in the query below -see red highlights. I made a few other changes in addition to those shown in red, but none of them should change the outcome.
One thing I noticed is that you are joining on address2_fax (which I assume is a fax number or address). It would be better to join on the primary key column(s).SELECT AA.[Country Code],
SUM(extendedamount) AS Amount
FROM AllActiveAuthorised AS AA
WHERE l2g_dispatcheddate >= CAST('20120901' as DATETIME)
AND l2g_dispatcheddate < CAST('20121001' as DATETIME)
AND EXISTS
(
SELECT *
FROM AllActiveAuthorised AB
WHERE AB.address2_fax = AA.address2_fax
AND AB.l2g_dispatcheddate < CAST('20120901' as DATETIME)
AND AB.[Country Code] = AA.[Country Code]
--AND [Country Code] = 'GB'
AND AB.allowmail = 'Allow'
AND AB.statuscode = '1'
AND AB.l2g_brandname = 'Y'
)
--AND [Country Code] = ('GB')
AND allowmail = 'Allow'
AND statuscode = '1'
AND l2g_brandname = 'Y'
GROUP BY
[Country Code]
ORDER BY
[Country Code]
Query with sub query
Posted - 10/01/2012 : 12:38:34
