title: SQL Server query : selecting a total figure, based on a sub-query
body: <p>I am trying to select total figures from my database table, using aggregate functions.</p>
  
  <p>The trouble is:  one of the columns I need requires that I run a sub-query within the aggregate. Which SQL does not allow.</p>
  
  <p>Here is the error I am getting :</p>
  
  <blockquote>
    <p>Cannot perform an aggregate function on an expression containing an aggregate or a subquery.</p>
  </blockquote>
  
  <p>Here is the initial query :</p>
  
  <pre><code> 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 &gt; '2015-05-31 23:59' and RES.created &lt;= '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 
  </code></pre>
  
  <p>GROUP BY 
         method</p>
  
  <p>(I used :  "<strong>etc, etc, etc</strong>"  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)</p>
  
  <p>This query worked just fine.</p>
  
  <p>However, I need an extra field -- a field for those customers whose data are "<strong>clean</strong>" --- meaning :  trimmed, purged of garbage characters (like : */?"#%), etc.</p>
  
  <p>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?</p>
  
  <p>This is the query I am using to "clean" customer data :</p>
  
  <pre><code>select * 
  from dbo.Customer 
  where 
      Len(LTRIM(RTRIM(streetAddress))) &gt; 5 and   
      Len(LTRIM(RTRIM(streetAddress))) &lt;&gt; '' and 
      (Len(LTRIM(RTRIM(streetAddress))) is not null and 
      Len(LTRIM(RTRIM(postalCode))) = 5 and postalCode &lt;&gt; '00000' and  
      postalCode &lt;&gt; '' and Len(LTRIM(RTRIM(postalCode))) is not null and 
      Len(LTRIM(RTRIM(postalOffice))) &gt; 2 and 
      phone &lt;&gt; '' and  Len(LTRIM(RTRIM(email))) &gt; 5 and 
      Len(LTRIM(RTRIM(email))) like '@' and 
      Len(LTRIM(RTRIM(firstName))) &gt; 2 and Len(LTRIM(RTRIM(lastName))) &gt; 2) and
      Len(LTRIM(RTRIM(firstName))) &lt;&gt; '-' and Len(LTRIM(RTRIM(lastName))) &lt;&gt;  '-' and
      Len(LTRIM(RTRIM(firstName))) is not null and 
      Len(LTRIM(RTRIM(lastName))) is not null
      etc, etc
  </code></pre>
  
  <p>This query works fine on its own.  </p>
  
  <p>But, how to <strong>INSERT</strong> it into the initial query, to create a separate field, where I can get the TOTAL of those customers who meet this "clean" criteria?</p>
  
  <p>I tried it like this :</p>
  
  <pre><code>select 
      method, 
      sum(payment_id) as payment_id, 
      sum(status) as status, 
      SUM((select * 
           from dbo.Customer 
           where 
              Len(LTRIM(RTRIM(streetAddress))) &gt; 5 and   
              Len(LTRIM(RTRIM(streetAddress))) &lt;&gt; '' and 
              (Len(LTRIM(RTRIM(streetAddress))) is not null and 
              Len(LTRIM(RTRIM(postalCode))) = 5 and 
              postalCode &lt;&gt; '00000' and postalCode &lt;&gt; '' and 
              Len(LTRIM(RTRIM(postalCode))) is not null and 
              Len(LTRIM(RTRIM(postalOffice))) &gt; 2 and phone &lt;&gt; '' and 
              Len(LTRIM(RTRIM(email))) &gt; 5 and 
              Len(LTRIM(RTRIM(email))) like '@' and 
              Len(LTRIM(RTRIM(firstName))) &gt; 2 and 
              Len(LTRIM(RTRIM(lastName))) &gt; 2) and 
              Len(LTRIM(RTRIM(firstName))) &lt;&gt; '-' and 
              Len(LTRIM(RTRIM(lastName))) &lt;&gt; '-' 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 &gt; '2015-05-31 23:59' and RES.created &lt;= '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
  </code></pre>
  
  <p>and it gave me that "aggregate" error.</p>