SELECT TOP(10) 嵌套 GROUP BY [英] SELECT TOP(10) with nested GROUP BY

问题描述

我有一个可以正常工作并提供结果的查询

I have a query which works fine and delivers the result

SELECT PartyName, Risk, SUM(CAST(Amount AS DECIMAL)) SubTotal
FROM CustomerData
GROUP BY PartyName, Risk

结果数据:

PartyName   Risk    SubTotal    

A           High    100
B           Med     25
A           Low     30
A           Med     70
B           Low     10

现在我想SUM每个方的总数并查看前 10 名.这样做时我遇到了 2 个问题:

Now I want to SUM the total for each party and view the top 10. I am running into 2 problems in doing so:

1.SUM 上的 TOP(10)

如果我执行以下操作:

SELECT TOP(10) PartyName, SUM(SubTotal) Total
FROM 
    (SELECT PartyName, Risk, SUM(CAST(Amount AS DECIMAL)) SubTotal
    FROM CustomerData
    GROUP BY PartyName, Risk) AS S
GROUP BY PartyName

我最终得到的是前 10 个而不是最高的 10 个

I end up with the first 10 and not the 10 highest sums

2.错误:

选择列表中的列S.Risk"无效，因为它既没有包含在聚合函数中，也没有包含在 GROUP BY 子句中.

Column 'S.Risk' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

当我尝试以下操作时:

SELECT TOP(10) PartyName, Risk, SUM(SubTotal) Total
FROM 
    (SELECT PartyName, Risk, SUM(CAST(Amount AS DECIMAL)) SubTotal
    FROM CustomerData
    GROUP BY PartyName, Risk) AS S
GROUP BY PartyName

我需要的是

PartyName   Risk    SubTotal    Total   

A           High    100         200
B           Med     25          35
A           Low     30          200
A           Med     70          200
B           Low     10          30

推荐答案

问题 1:

如果您想要最高的 10 个小计"，那么您需要一个 ORDER BY.

SELECT TOP(10) PartyName, SUM(SubTotal) Total
FROM 
    (SELECT PartyName, Risk, SUM(CAST(Amount AS DECIMAL)) SubTotal
    FROM CustomerData
    GROUP BY PartyName, Risk) AS S
GROUP BY PartyName
ORDER BY Total DESC

<小时>

问题 2:

这有点棘手，因为您想在对 SubTotal 求和时同时 GROUP BY PartyName 和 Risk>，但是您还想对每个 PartyName 的 SubTotal 求和而不将它们汇总.

---

Issue 2:

This gets a bit tricky, because you want to GROUP BY both PartyName and Risk while summing the SubTotal, however you also want to sum the SubTotal per PartyName without rolling them up.

一种方法是将表连接到另一个几乎相同的表，但是第二种方法将选择 Total per Party(忽略 风险完全)，这样我们就可以得到分组的总数.

One way to do this would be to join the table to another table that's nearly identical, however the second one will select the Total per Party (disregarding Risk entirely), so that we can get the grouped totals.

然后，我们可以将其与我们的初始查询 ON PartyName 合并，以获得一个返回汇总数据以及每个 重复 Total 的查询派对.

We can then merge that with our initial query ON PartyName to have a query that returns both the rolled-up data, as well as repeating Total per Party.

SELECT TOP(10) s.PartyName, s.Risk, s.SubTotal, s2.Total
FROM 
    (SELECT PartyName, Risk, SUM(CAST(Amount AS DECIMAL)) SubTotal
    FROM CustomerData
    GROUP BY PartyName, Risk) S
LEFT JOIN
    (SELECT PartyName, SUM(CAST(Amount AS DECIMAL)) Total
    FROM CustomerData
    GROUP BY PartyName) S2 
ON S.PartyName = S2.Partyname

这篇关于SELECT TOP(10) 嵌套 GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！