SQL Server query : selecting a total figure, based on a sub-query

Question

I am trying to select total figures from my database table, using aggregate functions.

The trouble is: one of the columns I need requires that I run a sub-query within the aggregate. Which SQL does not allow.

Here is the error I am getting :

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Here is the initial query :

 select 
     method, 
     sum(payment_id) as payment_id, 
     sum(status) as status,     
     sum(allowEmailContact) as allowEmailContact, 
     sum(allowPhoneContact) as allowPhoneContact, 
     sum(totalReservations) as totalReservations 
from 
    (SELECT 
         RES.method, count(*) as payment_id, 
         '' as status, '' as complete_data, 
         '' as allowEmailContact, '' as allowPhoneContact,
         '' as totalReservations
     FROM 
         Customer CUS
     INNER JOIN 
         Reservation RES ON CUS.id = RES.customerId
     WHERE 
         (RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15   
 23:59')
         AND RES.payment_id IS NOT NULL
         AND scope_id = 1
     GROUP BY 
         RES.method

     UNION ALL

      etc
      etc
    ) AS results 

GROUP BY method

(I used : "etc, etc, etc" to replace a large part of the query; I assume there is no need to write the entire code, as it is very long. But, the gist is clear)

This query worked just fine.

However, I need an extra field -- a field for those customers whose data are "clean" --- meaning : trimmed, purged of garbage characters (like : */?"#%), etc.

I have a query that does that. But, the problem is: how to insert this query into my already existing query, so I can create that extra column?

This is the query I am using to "clean" customer data :

select * 
from dbo.Customer 
where 
    Len(LTRIM(RTRIM(streetAddress))) > 5 and   
    Len(LTRIM(RTRIM(streetAddress))) <> '' and 
    (Len(LTRIM(RTRIM(streetAddress))) is not null and 
    Len(LTRIM(RTRIM(postalCode))) = 5 and postalCode <> '00000' and  
    postalCode <> '' and Len(LTRIM(RTRIM(postalCode))) is not null and 
    Len(LTRIM(RTRIM(postalOffice))) > 2 and 
    phone <> '' and  Len(LTRIM(RTRIM(email))) > 5 and 
    Len(LTRIM(RTRIM(email))) like '@' and 
    Len(LTRIM(RTRIM(firstName))) > 2 and Len(LTRIM(RTRIM(lastName))) > 2) and
    Len(LTRIM(RTRIM(firstName))) <> '-' and Len(LTRIM(RTRIM(lastName))) <>  '-' and
    Len(LTRIM(RTRIM(firstName))) is not null and 
    Len(LTRIM(RTRIM(lastName))) is not null
    etc, etc

This query works fine on its own.

But, how to INSERT it into the initial query, to create a separate field, where I can get the TOTAL of those customers who meet this "clean" criteria?

I tried it like this :

select 
    method, 
    sum(payment_id) as payment_id, 
    sum(status) as status, 
    SUM((select * 
         from dbo.Customer 
         where 
            Len(LTRIM(RTRIM(streetAddress))) > 5 and   
            Len(LTRIM(RTRIM(streetAddress))) <> '' and 
            (Len(LTRIM(RTRIM(streetAddress))) is not null and 
            Len(LTRIM(RTRIM(postalCode))) = 5 and 
            postalCode <> '00000' and postalCode <> '' and 
            Len(LTRIM(RTRIM(postalCode))) is not null and 
            Len(LTRIM(RTRIM(postalOffice))) > 2 and phone <> '' and 
            Len(LTRIM(RTRIM(email))) > 5 and 
            Len(LTRIM(RTRIM(email))) like '@' and 
            Len(LTRIM(RTRIM(firstName))) > 2 and 
            Len(LTRIM(RTRIM(lastName))) > 2) and 
            Len(LTRIM(RTRIM(firstName))) <> '-' and 
            Len(LTRIM(RTRIM(lastName))) <> '-' and 
            Len(LTRIM(RTRIM(firstName))) is not null and  
            Len(LTRIM(RTRIM(lastName))) is not null)  ) as clean_data,
    sum(allowEmailContact) as allowEmailContact, sum(allowPhoneContact) as   allowPhoneContact, 
    sum(totalReservations) as totalReservations 
from 
    (SELECT 
        RES.method, count(*) as payment_id, '' as status, 
        '' as complete_data, '' as allowEmailContact, 
        '' as allowPhoneContact, '' as totalReservations
     FROM Customer CUS
     INNER JOIN Reservation RES ON CUS.id = RES.customerId
     WHERE (RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15   
  23:59')
       AND RES.payment_id is not null and scope_id = 1
     GROUP BY RES.method

     UNION ALL

     etc
     etc
     etc

and it gave me that "aggregate" error.

Answer 1

SELECT COUNT(*) instead of SUM(), also, the WHERE Clause to clean the data is awful. There has to be a better way. Maybe mark the rows as clean when they're updated or as a batch job?