How do i group properly? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How do i group properly?

Author

Topic

thephill
Starting Member

2 Posts

Posted - 2015-03-02 : 20:15:16

hi,
im using SQL server 2008 R2 developer for this, the following sql statement was used on Product and ProductCategory table on AdventureWorksLT2008R2 database.

select  p.Color, SUM(p.ListPrice) as total, pc.Name from SalesLT.Product p 
inner join SalesLT.ProductCategory pc on p.ProductCategoryID=pc.ProductCategoryID
group by p.Color,pc.Name

and it does grouping like this

Multi 269.97 Bib-Shorts
NULL 120.00 Bike Racks
NULL 159.00 Bike Stands
NULL 23.97 Bottles and Cages
NULL 276.72 Bottom Brackets
Silver 213.00 Brakes
Multi 8.99 Caps
Silver 20.24 Chains
NULL 7.95 Cleaners
Black 836.97 Cranksets
Silver 212.95 Derailleurs
NULL 21.98 Fenders
NULL 553.20 Forks
Black 187.44 Gloves
NULL 591.12 Handlebars
NULL 261.22 Headsets
Black 34.99 Helmets
Blue 34.99 Helmets
Red 34.99 Helmets
Silver 54.99 Hydration Packs
Multi 199.96 Jerseys
Yellow 215.96 Jerseys
NULL 93.97 Lights
NULL 25.00 Locks
Black 27404.84 Mountain Bikes
Silver 26462.84 Mountain Bikes
Black 9391.99 Mountain Frames
Silver 9599.11 Mountain Frames
Grey 125.00 Panniers
Silver/Black 448.13 Pedals
NULL 44.98 Pumps
Black 16631.30 Road Bikes
Red 39652.64 Road Bikes
Yellow 12406.41 Road Bikes
Black 9180.82 Road Frames
Red 13586.47 Road Frames
Yellow 2974.15 Road Frames
NULL 356.70 Saddles
Black 449.93 Shorts
White 36.98 Socks
Black 224.97 Tights
NULL 214.31 Tires and Tubes
Blue 18107.43 Touring Bikes
Yellow 13248.03 Touring Bikes
Blue 5682.74 Touring Frames
Yellow 5682.74 Touring Frames
Blue 190.50 Vests
Black 3093.01 Wheels

but as you can see there are repeated rows of each color i.e black was repeated several times and so is blue etc. What i want to do is add all total of black and put it under just one black and add all totals of blue and put it under just one blue, this can be done using table variables but is there another way to do this?
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-03 : 00:42:17

GROUP BY GROUPING SETS ( (Color, Name), (Color) )

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-03 : 11:14:14

select p.Color, SUM(p.ListPrice) as total
from SalesLT.Product p
group by p.Color
--order by Color

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources