How do I include a related records count in a recordset?

[meumax]

I am developing an web based incident reporting system using ASP/VBScript which records incidents and remdial actions taken to remedy the incidents.

Each incident may have many actions associated with it. Each action may be associated with only one incident.

The schema of the incidents table goes "incidentID, description etc...".

The actions table goes "actionID, fkIncident, description, finnishDate". fkIncident is the foreign key for the incident table.

I am trying to write a list incidents page which will list all incidents but which also includes a column showing how many actions are currently associated with each incident and how many actions are finnished.

i.e.

incidentID| Description | Actions | Actions Completed
-----------------------------------------------------
1 |Lorem ipsum | 5 | 2
2 |Lorem ipsum | 4 | 0
3 |Lorem ipsum | 10 | 10

As you can see, the "actions" column should just be a count of all related records in the actions table and "actions completed" column should be a count of the same records where the finnishDate field is not null.

How can I write a query to return the results I want?

 

[bperry]

Here's a query that should work if your db is SQL Server.
If not, perhaps someone can look at this for us and adapt to whatever.

rgrds, etc
bp

Select
IncidentId,
Count(ActionId) as Actions,
SUM(CASE if finnishDate IS NULL then 0 Else 1 END)
as 'Act Comp'
From Incidents i INNER JOIN Actions a
ON i.IncidentId = a.fkIncident
Group By IncidentId
Order By IncidentId -- If that's the desired sequence

 

[bperry]

Pardon me, I forgot your description.
My apologies.
bp

Select
i.IncidentId,
i.Description,
Count(ActionId) as Actions,
SUM(CASE if finnishDate IS NULL then 0 Else 1 END)
as 'Act Comp'
From Incidents i INNER JOIN Actions a
ON i.IncidentId = a.fkIncident
Group By i.IncidentId, i.Description
Order By IncidentId -- If that's the desired sequence

 

[meumax]

Thanks for the help bperry.

Between the time I posted and you replied I worked out how to count the actions but got stuck on how to count how many actions were completed seeing as you can't 'count()' a 'Null' field. That 'Sum()' trick of yours is pretty neat, I've never used any of the procedural control structures of SQL b4.