sql - 在查询结果中插入行(总和)

Question

我有一份报告显示客户订购的产品及其价格:

CompanyA    Product 7    14.99  
CompanyA    Product 3    45.95
CompanyA    Product 4    12.00
CompanyB    Product 3    45.95
CompanyC    Product 7    14.99
CompanyC    Product 3    45.95

我想插入一行总结每个公司的订单，如下所示:

CompanyA    Product 7    14.99  
CompanyA    Product 3    45.95
CompanyA    Product 4    12.00
               Total:    72.94
CompanyB    Product 3    45.95
               Total:    45.95
CompanyC    Product 7    14.99
CompanyC    Product 3    45.95
               Total:    60.94

下面是一些代码，显示了我所拥有的查询的基本结构:

SELECT company
   , product
   , price
FROM companyMaster
ORDER BY company,
   , product,
   , price;

有谁知道如何做到这一点？我在 Transact-SQL (Microsoft SQL Server) 中写这个。

Answer 1

SELECT  company,
        product,
        SUM(price)
FROM    companyMaster
GROUP BY
        company, ROLLUP(product)