Insert line into a query result (sum)

问题:

I have a report that shows products that customers have ordered, along with their prices:

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

I'd like to insert a line that sums each company's order, like this:

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

Here's some code that shows the basic structure of the query I have:

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

Does anyone know how to do this? I'm writing this in Transact-SQL (Microsoft SQL Server).

回答1:

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

回答2:

Thanks for everyone's feedback/help, it at least got me thinking of different approaches. I came up with something that doesn't depend on what version of SQL Server I'm using (our vendor changes versions often so I have to be as cross-compliant as possible).

This might be considered a hack (ok, it is a hack) but it works, and it gets the job done:

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

UNION

SELECT company + 'Total'
   , ''
   , SUM(price)
FROM companyMaster
GROUP BY company

ORDER BY company;

This solution basically uses the UNION of two select statements. The first is exactly like the orginal, the second produces the sum line I needed. In order to correctly locate the sum line, I did a string concatenation on the company name (appending the word 'Total'), so that when I sort alphabetically on company name, the Total row will show up at the bottom of each company section.

Here's what the final report looks like (not exactly what I wanted but functionally equivalent, just not very pretty to look at:

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

回答3:

Since you are on SQL Server 2005 you need to use rollup like this.

-- cte for test data
;with companyMaster(company, product, price) as
(select 'CompanyA',    'Product 7',    14.99 union all  
 select 'CompanyA',    'Product 3',    45.95 union all
 select 'CompanyA',    'Product 4',    12.00 union all
 select 'CompanyB',    'Product 3',    45.95 union all
 select 'CompanyC',    'Product 7',    14.99 union all
 select 'CompanyC',    'Product 3',    45.95
)

select
  company,
  case when grouping(product) = 0
    then product
    else 'Total:'
  end,
  sum(price)
from companyMaster
group by company, product with rollup
having grouping(company) = 0