SQL Count Query Help (1 Viewer)

NearImpossible

Registered User.
Local time
Today, 02:35
Joined
Jul 12, 2019
Messages
170
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

SQL:
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

Registered User.
Local time
Today, 02:35
Joined
Mar 6, 2006
Messages
4,344
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
Code:
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]
 
Last edited:

NearImpossible

Registered User.
Local time
Today, 02:35
Joined
Jul 12, 2019
Messages
170
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
Code:
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

Registered User.
Local time
Today, 02:35
Joined
Mar 6, 2006
Messages
4,344
Keven,

Thanks for letting us know that worked for you.

Glad we could assist.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom