使用Group By获取总计

Question

我目前正在尝试学习SQL，正在使用SQL Server 2008数据库进行一些简单的销售报告，尝试使用SUM、COUNT、AVG和GROUP BY函数。我已经成功地获得了每个分组的总数、计数和平均值。

如何获得所有分组行的总和？

截至目前的SQL：

SELECT 
    SUM(dbo.tbl_orderitems.mon_orditems_pprice) AS prodTotal,
    AVG(dbo.tbl_orderitems.mon_orditems_pprice) AS avgPrice,
    count(dbo.tbl_orderitems.uid_orditems_prodid) AS prodQty,
    dbo.tbl_orderitems.txt_orditems_pname
FROM 
    dbo.tbl_orderitems
INNER JOIN 
    dbo.tbl_orders 
    ON (dbo.tbl_orderitems.uid_orditems_orderid = dbo.tbl_orders.uid_orders)
WHERE 
    dbo.tbl_orders.uid_order_webid = 
       <cfqueryparam cfsqltype="cf_sql_integer" value="#session.webid#">
    AND dbo.tbl_orders.txt_order_status = 
       <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.sale_status#">
GROUP BY
    dbo.tbl_orderitems.txt_orditems_pname

产品                              数量     总价        平均价
威斯特伯里攀爬框                      17      8,023.00    471.94
桑德波因特豪华攀爬框                  34      36,146.00   1,063.12
罗斯伯里攀爬框                       9       7,441.00    826.78
里奇维尤得克萨斯攀爬框                10      6,990.00    699
Selwood野餐桌                      9       489.92      54.44
我需要数量列和总价列的总和

非常感谢

Jason

- Jason Congerton

---

你能给一个你想要的输出的例子吗？我不是很明白你想要什么。 - Lock

请查看GROUPING SETS。 - Martin Smith

抱歉，我可能有点愚蠢，请问如何将带有制表符的数据输入到问题中？ - Jason Congerton

@JasonCongerton - 将其格式化为代码以保留间距。 - Martin Smith

Answer 1

你正在寻找ROLLUP运算符，它会在结果集的末尾添加一个总计行。如果你正在寻找更复杂的聚合总计，请使用带有GROUP BY子句的ROLLUP或CUBE，例如@MartinSmith提供的链接或Aggregation WITH ROLLUP。

SELECT 
    SUM(dbo.tbl_orderitems.mon_orditems_pprice) AS prodTotal,
    AVG(dbo.tbl_orderitems.mon_orditems_pprice) AS avgPrice,
    count(dbo.tbl_orderitems.uid_orditems_prodid) AS prodQty,
    dbo.tbl_orderitems.txt_orditems_pname
FROM 
    dbo.tbl_orderitems
INNER JOIN 
    dbo.tbl_orders ON (dbo.tbl_orderitems.uid_orditems_orderid = dbo.tbl_orders.uid_orders)
WHERE 
    dbo.tbl_orders.uid_order_webid = <cfqueryparam cfsqltype="cf_sql_integer" value="#session.webid#">
    AND dbo.tbl_orders.txt_order_status = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.sale_status#">
GROUP BY
    dbo.tbl_orderitems.txt_orditems_pname
WITH ROLLUP

- inevio

---

注意：WITH ROLLUP和WITH CUBE功能已被弃用，并建议避免使用。（请参见此手册页面的非ISO兼容语法部分）。您可以使用ROLLUP()替代：GROUP BY ROLLUP(dbo.tbl_orderitems.txt_orditems_pname)。 - Andriy M

那个很好用，而且Martin Smith关于聚合和Rollup的信息对我的下一步工作很有帮助 - 谢谢大家。 - Jason Congerton

Answer 2

我知道这是一个老问题，但是为了以后的参考 - 你也可以通过使用GROUPING SETS来获得更多对分组过程的控制。例如：

SELECT 
    SUM(dbo.tbl_orderitems.mon_orditems_pprice) AS prodTotal,
    AVG(dbo.tbl_orderitems.mon_orditems_pprice) AS avgPrice,
    count(dbo.tbl_orderitems.uid_orditems_prodid) AS prodQty,
    COALESCE(dbo.tbl_orderitems.txt_orditems_pname, 'TOTAL')
FROM 
    dbo.tbl_orderitems
INNER JOIN 
    dbo.tbl_orders 
    ON (dbo.tbl_orderitems.uid_orditems_orderid = dbo.tbl_orders.uid_orders)
WHERE 
    dbo.tbl_orders.uid_order_webid = 
       <cfqueryparam cfsqltype="cf_sql_integer" value="#session.webid#">
    AND dbo.tbl_orders.txt_order_status = 
       <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.sale_status#">
GROUP BY GROUPING SETS (
    (dbo.tbl_orderitems.txt_orditems_pname),
    ()
)

这样，结果将按txt_orditems_pname分组，并且不进行任何分组。您可以指定更多的分组集，例如：查询某个部门和团队的平均薪水，同时返回该部门、整个公司的总计。

- Apokralipsa

Answer 3

将您的选择包装在另一个选择器中，并对列进行求和。

- ilanco