0

I have a query that counts the number of records in a table that contains a number of repair tickets, and the result is the number of repair tickets by company that have been completed. Example; Companycode | CompnayName | CountOfTickets

SELECT 
Incident.CompanyCode, Count(Incident.CompanyCode) AS IncCount
FROM 
Incident
GROUP BY
Incident.CompanyCode, Incident.IncidentType, Incident.IncidentClosed
HAVING
(((Incident.IncidentType)="On - Site") AND 
((Incident.IncidentClosed)=True));

Now, what I am having a hard time getting my head wrapped around is this. Each of the records being counted has a field for marking the ticket as having been meeting an SLA. The field is a text field with either a "Y" or an "N".

What I am trying to do is count the number of records within the query above, that meets the criteria of either a "Y" or an "N", and then divide that result by the count of the main query to get a percentage of repair tickets that match the criteria. So, out of x number of repair tickets for company xyz, x% have met the SLA.

I know this would likely involve a sub-query, but I am not sure how to code it or where to put the sub-query.

I would like to have the result as part of the same result table ie.. Companycode | CompnayName | CountOfTickets | % Met SLA

I don't know if the solution would be a simple one or not, but I have been working on this for sometime now and I just can't see it.

Any help would be highly appreciated.

Thank you for taking the time to review this post and for any help that is given.

Greg

1 Answer 1

0
SELECT 
    CompanyName, 
    CompanyCode, 
    Count(*), 
    Sum(iif(MetSLA, 1, 0))/Count(*) * 100
FROM 
    Incident
WHERE 
    IncidentType = "On - Site" 
    AND IncidentClosed = Yes
GROUP BY 
    CompanyName, CompanyCode;

Explanation: the Sum(iif(MetSLA, 1, 0)) expression effectively counts rows where the MetSLA flag is set (true/yes). Sum is applied as an aggregate function after the rows are grouped (like Count is). Thus, you get a count of MetSLA=yes rows as a percentage of rows for that company (since the grouping is by company).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .