question

cdurham avatar image
cdurham asked

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
sumsub-total
subtotal.jpg (121.2 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

cdurham avatar image cdurham commented ·
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.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
The categories table was just used to build a test scenario - you should replace that with your table
0 Likes 0 ·
cdurham avatar image cdurham commented ·
Ok, Thanks!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.