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

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][1]

[1]: http://ask.sqlservercentral.com/storage/temp/172-subtotal.jpg
subtotal.jpg (124.2 kB)
more ▼

asked Apr 17 '12 at 02:54 PM in Default

cdurham gravatar image

230 21 22 24

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
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 (
       isnull(category, 'VEND REBATE') as col1, 
       sum(value) as col2,  
    from Categories
    group by rollup (col3, category)
where a.col3 is not null
order by col3, col2
more ▼

answered Apr 17 '12 at 03:13 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 43 49 76

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 17 '12 at 02:54 PM

Seen: 1198 times

Last Updated: Apr 18 '12 at 08:01 PM