title: Aggregate within an aggregate
body: <p>I am trying to use multiple aggregates in a single select statement and am stuck on the one that's value is based off of another aggregate (Total revenue which is calculated by multiplying <code>COUNT(TrackID)</code> by <code>Unit Price</code>).</p>
  
  <p>I am not allowed to do CTEs and have failed trying to use sub-queries. The three commented out lines are my failed attempts (not failed attempts at sub-queries, I deleted those already).</p>
  
  <pre><code>SELECT C.Country
      ,Ar.Name AS 'Artist Name'
      ,COUNT(T.TrackId) AS 'Track Count'
      ,COUNT(DISTINCT T.Name) AS 'Unique Track Count'
      ,COUNT(T.TrackID) - COUNT(DISTINCT T.Name) AS 'Count Difference'
      --,POWER(IL.UnitPrice,COUNT(T.TrackId)) AS 'Test'
      --,SUM(IL.UnitPrice * COUNT(T.TrackID)) AS 'Total Revenue'
      --,IL.UnitPrice * COUNT(T.TrackID) AS 'Total Revenue'
      ,CASE MediaTypeId
          WHEN 3
          THEN 'Video'
          ELSE 'Audio'
          END AS MediaType
  FROM Artist Ar
  JOIN Album Al
      ON Al.ArtistId = Ar.ArtistId
  JOIN Track T 
      ON T.AlbumId = Al.AlbumId
  JOIN InvoiceLine Il
      ON Il.TrackId = T.TrackId
  JOIN Invoice I
      ON I.InvoiceId = Il.InvoiceId
  JOIN Customer C
      ON C.CustomerId = I.CustomerId
  GROUP BY Ar.Name
      ,C.Country
      ,CASE MediaTypeId
          WHEN 3
          THEN 'Video'
          ELSE 'Audio'
          END
  ORDER BY C.Country ASC, COUNT(T.TrackID) DESC, Ar.Name ASC
  </code></pre>
  
  <p>I also tried this but results get left out</p>
  
  <pre><code>SELECT C.Country
      ,Ar.Name AS 'Artist Name'
      ,COUNT(T.TrackId) AS 'Track Count'
      ,COUNT(DISTINCT T.Name) AS 'Unique Track Count'
      ,COUNT(T.TrackId) - COUNT(DISTINCT T.Name) AS 'Count Difference'
      ,CASE MediaTypeId
          WHEN 3
          THEN 'Video'
          ELSE 'Audio'
          END AS MediaType
      ,T2.TotalRevenue
  FROM Artist Ar
  JOIN Album Al
      ON Al.ArtistId = Ar.ArtistId
  JOIN Track T
      ON T.AlbumId = Al.AlbumId
  JOIN InvoiceLine Il
      ON T.TrackId = Il.TrackId
  JOIN Invoice I
      ON I.InvoiceId = Il.InvoiceId
  JOIN Customer C
      ON C.CustomerId = I.CustomerId
  JOIN (SELECT T.TrackId, SUM(Il.TrackId * Il.UnitPrice) AS 'TotalRevenue'
      FROM Track T
      JOIN InvoiceLine Il 
          ON T.TrackId = Il.InvoiceId
          GROUP BY T.TrackId) T2 ON T.TrackId = T2.TrackId
  GROUP BY C.Country
      ,Ar.Name
      ,CASE MediaTypeId
          WHEN 3
          THEN 'Video'
          ELSE 'Audio'
          END
      ,T2.TotalRevenue
  ORDER BY C.Country ASC, COUNT(T.TrackID) DESC, Ar.Name ASC
  </code></pre>