时间戳

首页 > 解决方案 > 聚合内的聚合

sql-server - 聚合内的聚合

问题描述

我正在尝试在单个 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

标签： sql-serveraggregates

解决方案

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

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

推荐阅读