Showing two 'counts' on the same row

[Sam]

Suppose I have the following tables:

CUSTOMER
Customer_ID
Customer_Name

TICKETS
Ticket_ID
Ticket_Text
Ticket_Status (either 'closed' or 'open')

CUSTOMER_TICKETS (link table)
Customer_ID
Ticket_ID

This is database to allow customers to raise 'tickets'. What I want is a
query which shows the Customer ID, Customer Name, number of open tickets
and number of closed tickets all on the same row. What query would I need
to show this?

There is a many-to-many relationship meaning that a ticket can be linked
to multiple customers and a customer may be linked to multiple tickets.

Thanks

 

[John Spencer]

SELECT C.Customer_ID, C.Customer_Name
, Sum(IIF(T.Status='Closed',1,0)) as ClosedCount
, Sum(IIF(T.Status='Open',1,0)) as OpenCount
FROM (Customer as C INNER JOIN Customer_Tickets as CT
ON C.Customer_Id = CT.CustomerID)
INNER JOIN Tickets as T
ON CT.Ticket_ID = T.Ticket_Id
That will count the same ticket multiple times - once for each associated
customer.

It is a lot more complex to count a Ticket ONCE. And if you are counting at
ticket once which customer does the ticket get associated with? Say John is
linked to ticket 50 and Sue is linked to ticket 50
Do I count ticket 50 once for John or once for Sue or once for each of them?


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Sam]

SELECT C.Customer_ID, C.Customer_Name , Sum(IIF(T.Status='Closed',1,0))
as ClosedCount , Sum(IIF(T.Status='Open',1,0)) as OpenCount FROM
(Customer as C INNER JOIN Customer_Tickets as CT ON C.Customer_Id =
CT.CustomerID) INNER JOIN Tickets as T
ON CT.Ticket_ID = T.Ticket_Id
That will count the same ticket multiple times - once for each
associated customer.

It didn't work right away. Something about one of the variables not being
part of the aggregate function. I played around and used some existing
queries with the same ideas and eventually it worked.

It is a lot more complex to count a Ticket ONCE. And if you are
counting at ticket once which customer does the ticket get associated
with? Say John is linked to ticket 50 and Sue is linked to ticket 50 Do
I count ticket 50 once for John or once for Sue or once for each of
them?

If this was the required scenario then each Ticket would only be linked
to one customer in the first place so the query you posted above would
work just the same.

 

[Bob Barrows]

It didn't work right away. Something about one of the variables not
being part of the aggregate function. I played around and used some
existing queries with the same ideas and eventually it worked.

Unfortunately you didn't tell us about it. We could have easily advised you
to add the group-by clause that John forgot to include:
GROUP BY C.Customer_ID, C.Customer_Name

 

[John Spencer]

Whoops!
My fault. I forgot the Group By clause

SELECT C.Customer_ID, C.Customer_Name
, Sum(IIF(T.Status='Closed',1,0)) as ClosedCount
, Sum(IIF(T.Status='Open',1,0)) as OpenCount
FROM (Customer as C INNER JOIN Customer_Tickets as CT
ON C.Customer_Id = CT.CustomerID)
INNER JOIN Tickets as T
ON CT.Ticket_ID = T.Ticket_Id
GROUP BY C.Customer_ID, C.Customer_Name


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County