I have three tables in which two are master tables and other one is map. They are given below.
tbl_Category, having columns Id (PK) and Name
/*
ID NAME 1 Agriculture & Furtilizers 2 Apparel & Garments 3 Arts & Crafts 4 Automobiles
*/
tbl_SubCategory
/* Id SubCategoryName CategoryId (FK, PK of above) 2 Badges, Emblems, Ribbons & Allied 2 3 Barcodes, Stickers & Labels 2 4 Child Care & Nursery Products 2 9 Fabrics & Textiles 2 */
Now the third table is tbl_Company_Category_Map, where I am holding all categories and its subcategories of a company. below is its schema and data.
/*
CompanyCategoryId SubCategoryId CategoryId CompanyId
10 36 11 1
11 38 11 1
12 40 11 1
*/
Above, first column is the PK of tbl_Company_Category_Map, second column is PK of tbl_SubCategory and third one is PK of tbl_Category and last one is the company id. Now what i want is to display the display total companies listed in each subcategory of a category. Somethinglike this.
Subcategory Name Total COmpanies
Apparel, Clothing & Garments 1153
Badges, Emblems, Ribbons & Allied Products 4100
Barcodes, Stickers & Labels 998
Child Care & Nursery Products 2605
Cotton Bags, Canvas Bags, Jute Bags & Other Fabric Bags 2147
I am using query :
BEGIN
SELECT tbl_SubCategory.Name AS SubCategoryName, tbl_Category.Name AS CategoryName, TotalCompanies=(Select COUNT(*) From tbl_Company_Category_Map WHERE CategoryId = @Id)
FROM tbl_Category INNER JOIN
tbl_Company_Category_Map ON tbl_Category.Id = tbl_Company_Category_Map.CategoryId INNER JOIN
tbl_SubCategory ON tbl_Company_Category_Map.SubCategoryId = tbl_SubCategory.Id
WHERE (tbl_Company_Category_Map.CategoryId = @Id)
Group By tbl_SubCategory.Name , tbl_Company_Category_Map.CategoryId, tbl_Category.Name
ORDER BY tbl_Company_Category_Map.CategoryId
END
My Problem is that I am getting total number of companies same for each row. Please help me.
See Question&Answers more detail:os