February 17, 2013 at 7:29 am
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!
February 17, 2013 at 8:35 am
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]
February 17, 2013 at 10:48 pm
Thanks Steve! That was so easy I didn't even think to try it!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply