聚合中的聚合

Question

我试图在一条select语句中使用多个聚合，并被困在基于另一个聚合的值(总收入，其计算方法是COUNT(TrackID)乘以Unit Price)上。

我不被允许执行CTE，并且尝试使用子查询失败。被注释掉的三行是我失败的尝试(不是子查询的失败尝试，我已经删除了它们)。

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

我也试过了，但结果被遗漏了

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

Answer 1

你不能在计算中使用聚合，或者在同一查询的聚合中使用聚合，你需要将查询包装为内部查询，并在外部查询中使用聚合进行计算，如下所示：

Select [columnsNames], [aggregates]
From
(
Your Inner Query
)a
Group By [columnsNames]
Order By [columnsNames]