count Query in sql

Question

i have an issue with query

1st table (Master) Name :MainCategory with fields (Category_id,Category_name)

2nd Table (Transation) Name : Incident with fields (Incident_id,Category_id,subject,description)

i want to query to count the appearance of category_id in the table Transation

for eg result can be

Category_name   Number_of_Faults

Hardware          10
Software          22
Network           17

thanks

Kumar

Answer 1

Try this:

SELECT a.Category_Name, COUNT(b.Incident_Id) Number_of_Faults
FROM MainCategory a JOIN Incident b
ON a.Category_id = b.Category_id
GROUP BY a.Category_Name

Answer 2

Try this. You need a LEFT JOIN to deal with "no incidents" for a given category

SELECT
    M.Category_Name,
    COUNT(I.Category_id) AS Number_of_Faults
FROM
    MainCategory M
    LEFT JOIN
    Incident I ON M.Category_id = I.Category_id
GROUP BY
    M.Category_name