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

Marco · Accepted Answer · 2011-05-12 08:01:32Z

2

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

edited May 12, 2011 at 8:01

answered May 12, 2011 at 7:24

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

ypercubeᵀᴹ Over a year ago

In many RDBM systems, one has to write: GROUP BY a.Category_id, a.Category_Name

Marco Over a year ago

@ypercube: You're right, but OP didn't tell us which SQL type he's using... so I tried this believing it could be correct. Thanks :)

gbn Over a year ago

@Marco: Most RDBMS will reject this GROUP BY because it doesn't match the SELECT list. IIRC, only MySQL allows such ambiguity

ypercubeᵀᴹ Over a year ago

@Marco: It was not wrong before. But it would only work in systems (like MySQL) that support the inclusion (in the Select list) of fields that are functionally dependent on the fields in Group by list.

ypercubeᵀᴹ Over a year ago

@gbn: It's not ambiguity when a.Category_id is primary key and (therefore) a.Category_name is functionally dependent on it.

|

gbn · Accepted Answer · 2011-05-12 07:34:35Z

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

answered May 12, 2011 at 7:34

gbn

434k84 gold badges602 silver badges690 bronze badges

Collectives™ on Stack Overflow

count Query in sql

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related