Getting SUM of AVERAGE in single step SQL Server
<p>I have dataset which looks like below:</p>

<pre><code>cat subcat  amount
a   x1      1
a   x1      1
a   x1      1
a   x2      4
a   x2      4
a   x3      6
a   x4      3
a   x5      7
</code></pre>

<p>What I want the SQL query to do is to: </p>

<ol>
<li>Take the <code>AVG</code> value in each 'subcat' </li>
<li>Take the <code>SUM</code> at a 'cat' level</li>
</ol>

<p>The resulting dataset after step 1 should be :</p>

<pre><code>cat subcat  amount
a   x1      1
a   x2      4
a   x3      6
a   x4      3
a   x5      7
</code></pre>

<p>The resulting dataset after step 2 should be :</p>

<pre><code>cat amount
a   21
</code></pre>

<p>Now I know this can be easily achieved using a sub-query or creating a temporary table. But I want this to be done in a single step (Without using a sub-query.)</p>