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

what is your database system? And can you provide the result you are expecting? — deterministicFail, Aug 05 '14 at 14:24

score 0 · Answer 1 · answered Aug 05 '14 at 14:39

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'

Adrian · Answer 2 · 2014-08-05T14:51:29.963

0

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'

edited Aug 05 '14 at 14:51

answered Aug 05 '14 at 14:45

Adrian

`!=` is not a valid operator in all DBMS, only in some of them. – ypercubeᵀᴹ Aug 05 '14 at 15:13

Hogan · Answer 3 · 2014-08-05T15:45:49.117

0

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).

edited Aug 05 '14 at 15:45

answered Aug 05 '14 at 14:50

Hogan

They want a single result, not `GROUP BY` (as in bowlturner's answer.) – ypercubeᵀᴹ Aug 05 '14 at 15:11
@ypercube - you are probably right, I can't tell from the question. – Hogan Aug 05 '14 at 15:30
*"My query counts all records but **does not return the result in one row.** I get the results per request. "* – ypercubeᵀᴹ Aug 05 '14 at 15:43
@ypercube - My query did return one row, but I changed it to use the DISTINCT keyword instead of group by which might make you happier. :D – Hogan Aug 05 '14 at 15:46
@ypercube - I should point out, my query will return a different result than bowturner's. I'm not sure which the OP wants, I would expect my query, which is why I posted it. – Hogan Aug 05 '14 at 15:47

3 Answers3