title: How can I get distinct average and sum values in one MS SQL query?
body: <p>I am trying to create a query in MS SQL Server 2012 which gives me a <code>count</code>, <code>average</code> and some <code>sum</code> values of distinct records in a database table. I'll try to explain my situation and my wishes as best as I can. If there remains something unclear or if some extra information is needed, please let me know.</p>
  
  <p>Having the following table <code>TEMP</code> with 10 records:</p>
  
  <p><strong>TABLE</strong></p>
  
  <pre><code>╔════════╦═════════════╦════════╦═══════════╗
  ║ Number ║ DateOfBirth ║ Gender ║ Activity  ║
  ╠════════╬═════════════╬════════╬═══════════╣
  ║ 191806 ║ 1940-08-31  ║ F      ║ AMADMIN   ║
  ║ 196484 ║ 1940-09-23  ║ F      ║ AMHOST    ║
  ║ 199480 ║ 1949-10-16  ║ F      ║ AMTRAINER ║
  ║ 201089 ║ 1947-04-08  ║ M      ║ AMTRAINER ║
  ║ 204528 ║ 1950-05-02  ║ F      ║ AMHOST    ║
  ║ 226356 ║ 1966-04-12  ║ M      ║ AMADMIN   ║
  ║ 226356 ║ 1966-04-12  ║ M      ║ AMHOST    ║
  ║ 377599 ║ 1985-05-15  ║ F      ║ AMADMIN   ║
  ║ 377599 ║ 1985-05-15  ║ F      ║ AMHOST    ║
  ║ 395809 ║ 1980-03-03  ║ F      ║ AMADMIN   ║
  ╚════════╩═════════════╩════════╩═══════════╝
  </code></pre>
  
  <p>Now, consider running the following query:</p>
  
  <p><strong>SQL</strong></p>
  
  <pre><code>  SELECT COUNT([Number]) AS Number, ROUND(AVG(CAST(DATEDIFF(DAY, [DateOfBirth], GETDATE()) / 365.2425 AS FLOAT)), 1) AS AverageAge,
      SUM(CASE WHEN [Gender] = 'M' THEN 1 ELSE 0 END) AS Male,
      SUM(CASE WHEN [Gender] = 'F' THEN 1 ELSE 0 END) AS Female
    FROM [TEMP]
    WHERE [Activity] IN ('AMHOST', 'AMADMIN', 'AMTRAINER')
  </code></pre>
  
  <p>This query will give me the following result:</p>
  
  <p><strong>RESULT</strong></p>
  
  <pre><code>╔════════╦════════════╦══════╦════════╗
  ║ Number ║ AverageAge ║ Male ║ Female ║
  ╠════════╬════════════╬══════╬════════╣
  ║     10 ║ 57,3       ║    3 ║      7 ║
  ╚════════╩════════════╩══════╩════════╝
  </code></pre>
  
  <p>So far so good! But now for the tricky part. What I really want is this result for all <code>distinct</code> records in the table. That means calculating the average age and male/female counts for all persons minus the two "double" persons (having <code>Number</code> <code>226356</code> and <code>377599</code>). So I need a query which produces the following result:</p>
  
  <p><strong>WANTED RESULT</strong></p>
  
  <pre><code>╔════════╦════════════╦══════╦════════╗
  ║ Number ║ AverageAge ║ Male ║ Female ║
  ╠════════╬════════════╬══════╬════════╣
  ║      8 ║ 56,9       ║    2 ║      6 ║
  ╚════════╩════════════╩══════╩════════╝
  </code></pre>
  
  <p>I know how to get the <code>distinct</code> records for one piece of the query like so:</p>
  
  <p><strong>SQL</strong></p>
  
  <pre><code>  SELECT COUNT(DISTINCT([Number])) AS Number, ROUND(AVG(CAST(DATEDIFF(DAY, [DateOfBirth], GETDATE()) / 365.2425 AS FLOAT)), 1) AS AverageAge,
      SUM(CASE WHEN [Gender] = 'M' THEN 1 ELSE 0 END) AS Male,
      SUM(CASE WHEN [Gender] = 'F' THEN 1 ELSE 0 END) AS Female
    FROM [TEMP]
    WHERE [Activity] IN ('AMHOST', 'AMADMIN', 'AMTRAINER')
  </code></pre>
  
  <p>But this produces:</p>
  
  <p><strong>RESULT</strong></p>
  
  <pre><code>╔════════╦════════════╦══════╦════════╗
  ║ Number ║ AverageAge ║ Male ║ Female ║
  ╠════════╬════════════╬══════╬════════╣
  ║      8 ║ 57,3       ║    3 ║      7 ║
  ╚════════╩════════════╩══════╩════════╝
  </code></pre>
  
  <p>Now the <code>Number</code> count is good, but the <code>AverageAge</code>, <code>Male</code> and <code>Female</code> values are not right.</p>
  
  <p>My question is, how can I adjust my query in such way that I retrieve the values as shown in the <strong>WANTED RESULT</strong> set, if such a query is even possible to begin with?</p>