SQL Count Query Help (1 Viewer)

[NearImpossible]

I am trying to add a few new reports to my DB and am running into some issues with my count query below on one of them

SELECT FacilityNames.[Facility Name],COUNT (FacilityTickets.TicketID) as TotalTickets,COUNT (FacilityTickets.Resolved=1) as ClosedTickets
FROM FacilityTickets
JOIN FacilityNames
ON FacilityNames.FacilityID = FacilityTickets.FacilityID
GROUP BY FacilityNames.[Facility Name]

I am wanting to be able to run a report to show the Facility Name, total Tickets, total Closed Tickets, and then would calculate the Total Open Tickets, however it is erroring out as Incorrect Syntax near ')'

If I remove the =1 from COUNT (FacilityTickets.Resolved=1) as ClosedTickets, then the query runs, however it just gives me the same number as the Total Tickets.

Please advise what I am doing wrong.

thank you
Kevin

 

[HiTechCoach]

Kevin,



It looks like you are trying to use a WHERE condition within the select part of the SQL statement.

.If you want to get a count of the tickets whare Resolved=1 then try this:

The way I do this a lot is idea is to create a column called ClosedTickets that is calculated as IIf(FacilityTickets.Resolved=1, 1 , 0). The column will have a 1 if resolved otherwise it will be 0. Now all you have to do is sum it up.

The SQL will look like this

SELECT FacilityNames.[Facility Name],COUNT (FacilityTickets.TicketID) as TotalTickets, Sum (IIf(FacilityTickets.Resolved=1, 1 ,0) ) as ClosedTickets
FROM FacilityTickets
JOIN FacilityNames
ON FacilityNames.FacilityID = FacilityTickets.FacilityID
GROUP BY FacilityNames.[Facility Name]

[HiTechCoach edits: to add explanation]

 

[NearImpossible]

Kevin,



It looks like you are trying to use a WHERE condition within the select part of the SQL statement.

.If you want to get a count of the tickets whare Resolved=1 then try this:

The way I do this a lot is idea is to create a column called ClosedTickets that is calculated as IIf(FacilityTickets.Resolved=1, 1 , 0). The column will have a 1 if resolved otherwise it will be 0. Now all you have to do is sum it up.

The SQL will look like this

SELECT FacilityNames.[Facility Name],COUNT (FacilityTickets.TicketID) as TotalTickets, Sum (IIf(FacilityTickets.Resolved=1, 1 ,0) ) as ClosedTickets
FROM FacilityTickets
JOIN FacilityNames
ON FacilityNames.FacilityID = FacilityTickets.FacilityID
GROUP BY FacilityNames.[Facility Name]

[HiTechCoach edits: to add explanation]

Works perfect !!

thanks for your help

 

[HiTechCoach]

Keven,

Thanks for letting us know that worked for you.

Glad we could assist.