We help IT Professionals succeed at work.

Need to add 3 CatID, CatName

I asked this question and got a good answer but I didn't ask correctly.

I have a table
Categories
CategoriesID  CategoriesName
1                     Apparel
2                     Apparel Women
3                     Apparel Men
4                     Electronics
5                     Grocery

This table is joined to a Retailer table
RetailerID          RetailerName          CategoryID         CategoryName              Sales
1                        Special1s                 2                         Apparel Women             $234
2                        Preworn Goods        3                         Apparel Men                   $54
3                        All Parts                    4                         Electronics                     $123
4                        Intiments                  2                          Apparel Women             $343

Now the problem is that the client wants all the Apparel "type" combined in the report.  So instead of the report displaying 3 kinds of Apparel they want
Category                Total Retailers             Total Sales
Apparel                             3                                $631
Electronics                        1                                $123

My question is, how do I combine the 3 results into 1?  I need to retain the 3 categories for other reports as well.

 
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2013

Commented:
Try thisL

SELECT  Trim(Replace(Replace(CategoryName,"Women",""),Men,"") ) as Category, Count(RetailerID) AS TotalRetailers, Sum(Sales) AS TotalSales
FROM YourTable
GROUP BY Trim(Replace(Replace(CategoryName,"Women",""),Men,"") )      

Open in new window

Most Valuable Expert 2012
Top Expert 2013

Commented:
Sorry - you need quotes around "Men":

SELECT  Trim(Replace(Replace(CategoryName,"Women",""),"Men","") ) as Category, Count(RetailerID) AS TotalRetailers, Sum(Sales) AS TotalSales
FROM YourTable
GROUP BY Trim(Replace(Replace(CategoryName,"Women",""),"Men","") )  

Open in new window

napsternovaDeveloper

Author

Commented:
OK, the only issue I have is that there is no name now for the combined Categories.  The combined categories should be named Apparel.  I guess the other small issue is that the previous dta person has a category named Apparel with related data.  I can change the current "Apparel" to something else.
napsternovaDeveloper

Author

Commented:
Oops, nevermind that last comment.
Most Valuable Expert 2012
Top Expert 2013

Commented:
Those replace statements reduce the category names for the data you displayed to "Apparel" and "Electronics"

napsternovaDeveloper

Author

Commented:
Now I do have to do the same thing for another 2 categories. Department Stores & Junior Department Stores, I need the category to be Department Stores. How would I add them and retain the AS Category?
Most Valuable Expert 2012
Top Expert 2013
Commented:
You can try this:

SELECT  Trim(Replace(Replace(Replace(CategoryName,"Women",""),"Men",""),"Junior","") ) as Category, Count(RetailerID) AS TotalRetailers, Sum(Sales) AS TotalSales
FROM YourTable
GROUP BY Trim(Replace(Replace(Replace(CategoryName,"Women",""),"Men",""),"Junior","") )  

Open in new window


However, if you have many more 'special cases' like this, you should seriously consider either cleaning up your data or restructuring your table, and opening new questions about that if needed.
napsternovaDeveloper

Author

Commented:
Gotcha.  I actually did figure out how to add Junior to the SELECT statement.  I've tried so many different ways of querying, and combining these categories.  This seems to work.  I inquired about just doing away with the different types of Stores but they are insistent on keeping them for "future" reports.
You can also add to table Categories field with some SuperCategory (it could be reference to table). For example, it could be:
 CategoriesID  CategoriesName   SuperCategory
1                     Apparel                     Apparel
2                     Apparel Women        Apparel
3                     Apparel Men             Apparel
4                     Electronics                Electronics
5                     Grocery                     Grocery
It will work for any text in CategoriesName