Developing Matt

My Technical Journal

Pivot Tables vs Sum w/ GROUP BY; Rollups vs Cube

leave a comment »

This week I had the pleasure of working on my bosses old stored procedures. I stress pleasure becuase it’s with both angst and anticipation. Angst, because I knew that I was going to have to just dive in to make sense of things. Anticipation, because I knew that I was going to have to just dive in to make sense of things. Hehe. In the end I learned a great deal, so I was happy. I just hope I’ll be able to not forget it AGAIN next month!

http://www.devx.com/dbzone/Article/28165/0/page/1
The above is where I learned how to do a Pivot command. The below is how I learned that a Pivot is the same thing as doing a SUM w/ GROUP BY.

SELECT storenum,category,
total = ISNULL([total],0),
fullhouse = ISNULL([full house],0),
hand = ISNULL([hand],0),
FROM #myTable
PIVOT (
SUM(qty)
FOR item in ([total],[Full House],[hand])
) AS dspPiv
ORDER BY column1

THE ABOVE PRODUCES THE SAME AS BELOW

SELECT StoreNum, MIN(category) AS Category,
SUM(qty) AS Total,
SUM(CASE WHEN RTRIM(item) = ‘Full house’ THEN qty ELSE 0 END) AS FullHouse,
SUM(CASE WHEN RTRIM(item) = ‘Hand’ THEN qty ELSE 0 END) AS Hand,
FROM #myTable
GROUP BY column1

Working with CUBE and/or ROLLUP:
http://databases.about.com/od/sql/l/aacuberollup.htm

I found the above article to sum it up pretty good. But what i find as the difference between a ROLLUP and CUBE is that both the ROLLUP and CUBE will sum up your data based on your GROUPING. However, the CUBE will go a bit FURTHER than the ROLLUP as it will show a total based on every combination of your GROUP BY. However, if you’re just doing a simple GROUP BY, with just one GROUP, or multiple GROUPS but with only one possible combination, than use a ROLLUP. The ROLLUP will eliminate showing any values with NULL in the first column.

Advertisements

Written by wadewalker

February 8, 2008 at 2:05 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: