依靠工作历史记录(Count on work history log)

 我有一个表request ： 
 
 
 和一个表requesthx ： 
 
 
 单个请求可以由多个技术人员进行许多日志更新。 例如，tech1可以为初始手机联系人创建日志，并在日志部分添加备注。 然后tech2可以记录这些笔记并完成部分工作要求。 Tech 3也可以在同一个工作上等待预约。 
 
 如果tech3已登录，我想显示tech3的每张打开票的计数。 
 
 完成此任务的最佳方法是什么？ 
 我已经完成了几个丑陋的查询，这些查询在foreach循环中运行查询以添加到count变量，但这似乎是达到简单计数总数的很长的路要走。 
 
 我试过了... 
 
SELECT requesthx.hxID, requesthx.requestID,
    requesthx.datetime_gmt, requesthx.log, requesthx.techID, requesthx.status,
COUNT($requestTable.requestID) AS tickets, request.status, requesthx.techID
FROM requesthx
LEFT JOIN request 
  ON (requesthx.requestID = request.requestID)
    WHERE (requesthx.status <> 'closed'
    AND request.status = 'open'
    AND requesthx.techID = '1')
GROUP BY requesthx.techID;
 
 ...在一个查询中有5张打开的票据，其中techID = 1.我只收到1作为计数。 有任何想法吗？ 

I have a table request:
 
 
and a table requesthx:
 
 
A single Request can have many log updates by multiple techs. For example, tech1 can create a log for the initial phone contact and add notes in the log section. Then tech2 could take those notes and complete a portion of the job requirement. Tech 3 could also be working on the same job waiting for a scheduled appointment.
 
If tech3 is logged in, I'd like to display a count of every open ticket for tech3.
 
What is the best way to accomplish this?
 I've completed several ugly queries that had queries running within foreach loops to add to a count variable, but this seems like the really long way to reach a simple count total.
 
I've tried...
 
SELECT requesthx.hxID, requesthx.requestID,
    requesthx.datetime_gmt, requesthx.log, requesthx.techID, requesthx.status,
COUNT($requestTable.requestID) AS tickets, request.status, requesthx.techID
FROM requesthx
LEFT JOIN request 
  ON (requesthx.requestID = request.requestID)
    WHERE (requesthx.status <> 'closed'
    AND request.status = 'open'
    AND requesthx.techID = '1')
GROUP BY requesthx.techID;
 
...on a query that had 5 open tickets with techID = 1. I only receive 1 as the count. Any ideas?

原文：https://stackoverflow.com/questions/29135609