SQL combine count results

Question

I have two large tables called REQUESTS and OPENISSUES. One holds request information and the other holds the number of issues per request.

In the REQUESTS table there is a status column that keeps track of the request status.

I'm looking for a Query that counts all records in the OPENISSUES table for all requests that do not have the status set to "Closed"

My query counts all records but does not return the result in one row. I get the results per request. Anyone has an idea?

Here is my query:

SELECT
(SELECT COUNT(ID) FROM openissues WHERE openissues.requestID=requests.ID) AS TotalIssues
FROM requests
WHERE requests.status<>'Closed'

Any idea is welcome at this point. Otherwise I will start to count the records in ASP.

Regards, Frits

Answer 1

I think what you need is simply a join:

  SELECT count(*) as TotalIssues
  FROM openissues o
  INNER JOIN requests r ON ( o.requestID = r.ID )
  WHERE r.status != 'Closed'

Answer 2

SELECT count(DISTINCT openissues.requestID)  
FROM openissues
INNER JOIN requests ON openissues.requestID = requests.ID 
WHERE rq.status <> 'Closed' 

or

SELECT count(*)
FROM openissues
INNER JOIN requests ON openissues.requestID = requests.ID 
WHERE rq.status <> 'Closed' 
GROUP BY openissues.requestID

These queries will count the number of requestIDs which have at least one record not in the Closed state in the requests table.

Note to @ypercube

It will not count the same requestID twice (as bowlturner's might).

Answer 3

This is certainly what it sounds like you want.

SELECT count(*) 
FROM openissues op, requests rq 
WHERE op.requestID = rq.ID and rq.status <> 'Closed'

or

SELECT count(*) 
FROM openissues op INNER JOIN requests rq ON op.requestID = rq.ID
WHERE rq.status <> 'Closed'