如何在SQL中同时使用SUM和MAX函数

Question

SELECT DISTINCT 
    G.NAME AS GENRE, 
    COUNT(T.NAME) AS TotalTransactions, 
    f.country, SUM(il.unitprice) AS Spent
FROM 
    final F
JOIN 
    CUSTOMER C ON C.CUSTOMERID = F.CUSTOMERID
JOIN 
    INVOICE I ON I.CUSTOMERID = C.CUSTOMERID
JOIN 
    INVOICELINE IL ON IL.INVOICEID = I.INVOICEID
JOIN 
    TRACK T ON T.TRACKID = IL.TRACKID
JOIN 
    GENRE G ON G.GENREID = T.GENREID
GROUP BY 
    G.NAME, c.country
ORDER BY 
    G.NAME;

期望产出：

 GENRE           TotalTransactions   Country   Spent
 ----------------------------------------------------
 Alternative              5          USA         4.95
 Alternative & Punk      36          Canada     35.64

例如，我只想选择另类音乐，因为他们花在另类音乐上的钱最多。

我尝试使用MAX(SUM())，但这给了我一个组错误。谢谢!

Answer 1

我认为一种更好的方法是对每种类型的和进行子查询，然后再查询一次，将每种类型的总数排序到前一位。

;WITH Normalized AS
(
    SELECT  
        G.NAME AS GENRE, 
        COUNT(T.NAME) AS TotalTransactions, 
        f.country, SUM(il.unitprice) AS Spent       
    FROM 
        final F
    JOIN 
        CUSTOMER C ON C.CUSTOMERID = F.CUSTOMERID
    JOIN 
        INVOICE I ON I.CUSTOMERID = C.CUSTOMERID
    JOIN 
        INVOICELINE IL ON IL.INVOICEID = I.INVOICEID
    JOIN 
        TRACK T ON T.TRACKID = IL.TRACKID
    JOIN 
        GENRE G ON G.GENREID = T.GENREID
    GROUP BY 
        G.NAME, c.country
)
,Ranked AS
(
    SELECT
        *,
        RANK() OVER (PARTITION BY GENRE ORDER BY Spent DESC) AS GenreRank
    FROM
        Normalized  
)
SELECT
    *
FROM
    Ranked  
WHERE
    GenreRank = 1
ORDER BY 
    [NAME]; 

甚至更好

;WITH Normalized AS
(
   ...
)
SELECT
    *
FROM
   Normalized   
HAVING RANK() OVER (PARTITION BY GENRE ORDER BY Spent DESC) = 1