Getting count from sql tables

I have three tables in which two are master tables and other one is map. They are given below.

  1. tbl_Category, having columns Id (PK) and Name

  2. CQRS: write to RDBMS, read from NoSQL?
  3. Calculate week of month starting Monday
  4. How to INSERT INTO table column with string/variable
  5. Microsoft SQL Server random row
  6. T-SQL Move From Test To Production Dynamically
  7. sp_executesql causing my query to be very slow
  8. /*

    ID      NAME
    1   Agriculture & Furtilizers
    2   Apparel & Garments
    3   Arts & Crafts   
    4   Automobiles
    

    */

  9. 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.

3 Solutions collect form web for “Getting count from sql tables”

Try this:

BEGIN


SELECT     tbl_SubCategory.Name AS SubCategoryName, COUNT(*) AS TotalCompanies
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
ORDER BY tbl_SubCategory.Name

END

Try changing this:

TotalCompanies=(Select COUNT(*) From tbl_Company_Category_Map WHERE CategoryId = @Id)

…to this…

TotalCompanies=COUNT(*)

You are already grouping by (what I think are) the right fields, and your WHERE clause will apply the CategoryID filter for you.

I think you over-complicated that quite a bit… give this a shot:

select sc.Name, count(*) as company_count
from tbl_Category c
join tbl_SubCategory sc on c.ID = sc.CategoryID
join tbl_Company_Category_Map map on sc.ID = map.SubCategoryID
where c.ID = @ID
group by sc.Name
MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.