Geek Answers Handbook

Insert a row in the query result (amount)

I have a report showing the products ordered by customers, as well as 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 would like to insert a line that sums up each company order, for example:

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 is some code that shows the basic structure of the request from me:

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

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

+5
source share
3 answers

Thanks for the feedback and help, at least I thought of different approaches. I came up with something that does not depend on which version of SQL Server I use (our provider often changes versions, so I have to be as cross-compatible as possible).

(, ), , :

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;

UNION select. , , . , ( "Total" ), , , Total ,

( , , , :

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
SELECT  company,
        product,
        SUM(price)
FROM    companyMaster
GROUP BY
        company, ROLLUP(product)
+4

SQL Server 2005, rollup, .

-- 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
+2

More articles:


All Articles