Get number of unique values in group of columns

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » T-SQL (SS2K8) » Get number of unique values in group of...

Add to briefcase

Get number of unique values in group of columns

Expand / Collapse

Author

Message

Posted Sunday, February 17, 2013 7:29 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:07 AM
Points: 72, Visits: 244

Hi -

I have the following scenario and not sure of the best way to write the T-SQL query without a bunch of joins.

DATA

Main Category | SubCategory | SubCategory2 | SubCategory3 | Product Name | Manufacturer | Color

Cloths | Shirts | Summer Shirts | Tank Tops | Tank Top Product #1 | The Best Manufacturer | White
Cloths | Shirts | Summer Shirts | Tank Tops | Tank Top Product #1 | The Best Manufacturer | Black
Cloths | Shirts | Summer Shirts | Tank Tops | Tank Top Product #1 | The Best Manufacturer | Green

Cloths | Shirts | Summer Shirts | T-Shirts | T-Shirt Product #1 | Some Manufacturer | White
Cloths | Shirts | Summer Shirts | T-Shirts | T-Shirt Product #2 | Some Manufacturer | White
Cloths | Shirts | Summer Shirts | T-Shirts | T-Shirt Product #3 | Some Manufacturer | White

Cloths | Pants | Summer Pants | Cargo Shorts | Cargo Short Product #1 | Some Manufacturer | White
Cloths | Pants | Summer Pants | Cargo Shorts | Cargo Short Product #2 | Some Manufacturer | Green
Cloths | Pants | Summer Pants | Cargo Shorts | Cargo Short Product #3 | Some Manufacturer | White

Food | Snacks | Candy Bars | NULL | Almond Joy | Hersheys | NULL
Food | Snacks | Candy Bars | NULL | Snickers | MARS | NULL
Food | Snacks | Chips | NULL | BBQ Pringles | Kellogg | NULL

Desired Result

I want the number of unique products, manufacturers and colors for each group of categories. Example below:

Main Category | SubCategory | SubCategory2 | SubCategory3 | Product Name | Manufacturer | Color

Cloths | Shirts | Summer Shirts | Tank Tops | 1 | 1 | 3
Cloths | Shirts | Summer Shirts | T-Shirts | 3 | 1 | 1
Cloths | Pants | Summer Pants | Cargo Shorts | 3 | 1 | 2
Food | Snacks | Candy Bars | NULL | 2 | 2 | NULL
Food | Snacks | Chips | NULL | 1 | 1 | NULL

Description of results:

There is one product (Tank Top Product #1), one manufacturer (The Best Manufacturer) and three colors (White, Black and Green) in the Cloths>Shirts>Summer Shirts>Tank Tops category grouping

There are three products (T-Shirt Product #1, T-Shirt Product #2 and T-Shirt Product #3), one manufacturer (Some Manufacturer) and one color (White) in the Cloths>Shirts>Summer Shirts>T-Shirts category grouping.

There are three products (Cargo Short Product #1, Cargo Short Product #1 and Cargo Short Product #1), one manufacturer (Some Manufacturer) and two colors (White and Green) in the Cloths>Pants>Summer Pants>Cargo Shorts category grouping.

There are two products (Almond Joy and Snickers), two manufacturers (Hersheys and MARS) and no colors in the Food>Snacks>Candy Bars category grouping.

There is one product (BBQ Pringles), one manufacturer (Kellogg) and no colors in the Food>Snacks>Chips category grouping.

Thanks in advance for any guidance on best ways to write a query such as this!

Post #1420977

Posted Sunday, February 17, 2013 8:35 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:40 AM
Points: 50, Visits: 230

Did you want a null for no colours or a zero?


select	[Main Category]
	,	[SubCategory]
	,	[SubCategory2]
	,	[SubCategory3]
	,	[Distinct Products] = COUNT(distinct [Product Name] )
	,	[Distinct Manufacturers] = COUNT (distinct [Manufacturer] )
	--- Choose which answer 
	,	[Distinct Colours] = nullif( COUNT(distinct [Color] ) , 0)
	,	[Distinct Colours] = COUNT(distinct [Color] ) 

from #t

group by [Main Category]
	,	[SubCategory]
	,	[SubCategory2]
	,	[SubCategory3]
	
order by [Main Category]
	,	[SubCategory]
	,	[SubCategory2]
	,	[SubCategory3]

Post #1420984

Posted Sunday, February 17, 2013 10:48 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:07 AM
Points: 72, Visits: 244

Thanks Steve! That was so easy I didn't even think to try it!

Post #1421032

« Prev Topic | Next Topic »

Add to briefcase

Permissions

Expand / Collapse