Is there a way to create a total or subtotal by category using SQL 2008 R2?

Question

The picture below shows categories (comp upg, comp ftr, etc.) and a sum total (Vend_Rebate). The process below was created using FileMaker Pro 11. My company is getting away from FileMaker, and I will now have to do this process using SQL Server 2008 R2.

I need to know if there is a way to add up the comp fields to get the sum as a vend rebate field. I used cube and rollup withou success. Any help would be appreciated.

alt text

Answer 1

0

Not sure of your underlying data, but here's a mockup that shows how you could use ROLLUP

if object_id('Categories','U') is not null drop table Categories
go
create table Categories 
   (category varchar(50), value decimal(8,2), col3 varchar(5))

insert into Categories select 'COMP_UPG', -1687, 'DFW03'
insert into Categories select 'COMP_FTR', -449.91, 'DFW03'
insert into Categories select 'COMP_PPD ACT', -35, 'DFW03'
insert into Categories select 'COMP_UPG', -749, 'DFW04'
insert into Categories select 'COMP_FTR', -449.91, 'DFW04'

select * from (
    select 
       isnull(category, 'VEND REBATE') as col1, 
       sum(value) as col2,  
       col3
    from Categories
    group by rollup (col3, category)
)a
where a.col3 is not null
order by col3, col2

answered Apr 17 '12 at 03:13 PM

Kev Riley ♦♦
46.1k ● 39 ● 43 ● 69

Thanks! I have one question. The picture that I used in the example were just a few rows. The actual data will consist of about 105 rows. I have the table with the 105 rows in my database already. Is there a way to do an insert into the categories table without having to write 105 insert into statements? Thanks.

Apr 17 '12 at 04:27 PM cdurham

The categories table was just used to build a test scenario - you should replace that with your table

Apr 18 '12 at 03:53 PM Kev Riley ♦♦

Ok, Thanks!

Apr 18 '12 at 08:01 PM cdurham

(comments are locked)

Is there a way to create a total or subtotal by category using SQL 2008 R2?

Follow this question

Related Questions