Group with Rollups??

Question

Post reply

Group with Rollups??

iruagawal

SSC Enthusiast

Points: 116
More actions
April 22, 2010 at 5:49 pm

#220791

Hey forumites,
Below is a ddl for a problem i am having. I want to sum up the total for each good below if they are the same. They have to have the same "goodsid","goodsname" and "goodsproducer" to be considered the same. For example, all goods with x500, apple, applemaker are all the same. I need to sum up each qty and price to get a total for each good. Will the grouping with rollup work with this? Is there any other way?
I have also attached a ddl of the desired result. Thanks in advance
create table #test
(
nameofbuyer varchar(50),
vinnumber varchar(50),
qty int,
goodsid varchar(100),
goodsname varchar (100),
goodsproducer varchar(100),
price money
)
insert into #test (nameofbuyer, vinnumber, qty, goodsid, goodsname, goodsproducer, price)
select 'domantfarms', '551155', 3, 'x500', 'apple', 'applemaker', 200 union all
select 'domantfarms', '551155', 3, 'x500', 'apple', 'applemaker', 200 union all
select 'domantfarms', '551155', 3, 'x500', 'apple', 'applemaker', 200 union all
select 'domantfarms', '552255', 3, 'y500', 'oranges', 'orangemaker', 300 union all
select 'domantfarms', '552255', 3, 'y500', 'oranges', 'orangemaker', 300 union all
select 'domantfarms', '553355', 3, 'z500', 'banana', 'bananamaker', 400 union all
select 'domantfarms', '553355', 3, 'z500', 'banana', 'bananamaker', 400 union all
select 'domantfarms', '553355', 3, 'z500', 'banana', 'bananamaker', 400 union all
select 'domantfarms', '553355', 3, 'z500', 'banana', 'bananamaker', 400 union all
select 'domantfarms', '554455', 3, 'w500', 'corn', 'cornmaker', 500 union all
select 'domantfarms', '554455', 3, 'w500', 'corn', 'cornmaker', 500 union all
select 'domantfarms', '554455', 3, 'w500', 'corn', 'cornmaker', 500
create table #desiredresult
(
nameofbuyer varchar(50),
vinnumber varchar(50),
qty int,
goodsid varchar(100),
goodsname varchar (100),
goodsproducer varchar(100),
price money
)
insert into #desiredresult (nameofbuyer, vinnumber, qty, goodsid, goodsname, goodsproducer, price)
select 'domantfarms', '551155', 3, 'x500', 'apple', 'applemaker', 200 union all
select 'domantfarms', '551155', 3, 'x500', 'apple', 'applemaker', 200 union all
select 'domantfarms', '551155', 3, 'x500', 'apple', 'applemaker', 200 union all
select 'Total', '551155', 9, 'x500', 'apple', 'applemaker', 600 union all
select 'domantfarms', '552255', 3, 'y500', 'orange', 'orangemaker', 300 union all
select 'domantfarms', '552255', 3, 'y500', 'orange', 'orangemaker', 300 union all
select 'Total', '552255', 6, 'y500', 'orange', 'orangemaker', 600 union all
select 'domantfarms', '553355', 3, 'z500', 'banana', 'bananamaker', 400 union all
select 'domantfarms', '553355', 3, 'z500', 'banana', 'bananamaker', 400 union all
select 'domantfarms', '553355', 3, 'z500', 'banana', 'bananamaker', 400 union all
select 'domantfarms', '553355', 3, 'z500', 'banana', 'bananamaker', 400 union all
select 'Total', '553355', 12, 'z500', 'banana', 'bananamaker', 1600 union all
select 'domantfarms', '554455', 3, 'w500', 'corn', 'cornmaker', 500 union all
select 'domantfarms', '554455', 3, 'w500', 'corn', 'cornmaker', 500 union all
select 'domantfarms', '554455', 3, 'w500', 'corn', 'cornmaker', 500 union all
select 'Total', '554455', 9, 'w500', 'corn', 'cornmaker', 1500
select * from #test
select * from #desiredresult
April 23, 2010 at 12:57 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150457 More actions · Answer 1

WITH Numbered

AS (

SELECT *,

rn = ROW_NUMBER() OVER (ORDER BY (SELECT 0))

FROM #test T

)

SELECT nameofbuyer = CASE WHEN GROUPING(T.nameofbuyer) = 0 THEN T.nameofbuyer ELSE 'Total' END,

vinnumber = CASE WHEN GROUPING(T.vinnumber) = 0 THEN T.vinnumber ELSE MAX(T.vinnumber) END,

qty = CASE WHEN GROUPING(T.qty) = 0 THEN T.qty ELSE SUM(T.qty) END,

T.goodsid,

T.goodsname,

T.goodsproducer,

price = CASE WHEN GROUPING(T.price) = 0 THEN T.price ELSE SUM(T.price) END

FROM Numbered T

GROUP BY

GROUPING SETS

(

(rn, nameofbuyer, vinnumber, qty, goodsid, goodsname, goodsproducer, price),

(goodsid, goodsname, goodsproducer)

);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

iruagawal SSC Enthusiast Points: 116 More actions · Answer 2

Thanks Stewart, thanks Paul. Wow Paul, I am speechless. What a solution. Never worked with grouping sets before. Thanks for the introduction.

iruagawal SSC Enthusiast Points: 116 More actions · Answer 3

One more thing, it worked just fine. But what slight tweak would you make if you

a)wanted a grand total at the end (im thinking, throw results in a temp table and sum up "price" column where "nameofbuyer" = total) is this the best way?

b) wanted to leave spaces on the total line in every column but the "qty" and "price"

here is a ddl of what the expected results

create table #test

(

nameofbuyer varchar(50),

vinnumber varchar(50),

qty int,

goodsid varchar(100),

goodsname varchar (100),

goodsproducer varchar(100),

price money

)

insert into #test (nameofbuyer, vinnumber, qty, goodsid, goodsname, goodsproducer, price)

select 'domantfarms', '551155', 3, 'x500', 'apple', 'applemaker', 200 union all

select 'domantfarms', '552255', 3, 'y500', 'oranges', 'orangemaker', 300 union all

select 'domantfarms', '553355', 3, 'z500', 'banana', 'bananamaker', 400 union all

select 'domantfarms', '554455', 3, 'w500', 'corn', 'cornmaker', 500 union all

select 'domantfarms', '554455', 3, 'w500', 'corn', 'cornmaker', 500

create table #desiredresult

(

nameofbuyer varchar(50),

vinnumber varchar(50),

qty int,

goodsid varchar(100),

goodsname varchar (100),

goodsproducer varchar(100),

price money

)

insert into #desiredresult (nameofbuyer, vinnumber, qty, goodsid, goodsname, goodsproducer, price)

select 'domantfarms', '551155', 3, 'x500', 'apple', 'applemaker', 200 union all

select 'Total', '', 9, '', '', '', 600 union all

select 'domantfarms', '552255', 3, 'y500', 'orange', 'orangemaker', 300 union all

select 'Total', '', 6, '', '', '', 600 union all

select 'domantfarms', '553355', 3, 'z500', 'banana', 'bananamaker', 400 union all

select 'Total', '', 12, '', '', '', 1600 union all

select 'domantfarms', '554455', 3, 'w500', 'corn', 'cornmaker', 500 union all

select 'Total', '', 9, '', '', '', 1500 union all

select 'Grand Total', '', 36, '', '', '', 4300

select * from #test

select * from #desiredresult

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

iruagawal (4/23/2010)
Thanks Stewart, thanks Paul. Wow Paul, I am speechless. What a solution. Never worked with grouping sets before. Thanks for the introduction.

Now what you need to do is lookup GROUP BY and the WITH ROLLUP and WITH CUBE options. The GROUPING() that Paul used is in the WITH CUBE documentation and it works with ROLLUP as well. Then, you'll be able to work out your "tweek" on your own. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 5

Exactly, Jeff. Doing is an important part of learning!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

iruagawal SSC Enthusiast Points: 116 More actions · Answer 6

iruagawal

SSC Enthusiast

Points: 116

April 26, 2010 at 9:36 am

#1157702

Seen. Thanks for the help guys.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

You bet. Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions