SQL Counting Records with Count and Having

Question

I'm having problems with what I thought was a simple query to count records:

SELECT req_ownerid, count(req_status_lender) AS total6 
FROM bor_requests
WHERE (req_status_lender = 0 AND req_status_borrower = 0) OR 
      (req_status_lender = 1 AND req_status_borrower = 1)
GROUP BY req_ownerid 
HAVING req_ownerid = 70

I thought this would count all the records where (req_status_lender = 0 AND req_status_borrower = 0) and (req_status_lender = 1 AND req_status_borrower = 1) and then give me the total but it only gives me the total for either (req_status_lender = 0 AND req_status_borrower = 0) or (req_status_lender = 1 AND req_status_borrower = 1).

Any ideas what I'm doing wrong?

If you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! — marc_s
– marc_s, Commented Feb 6, 2011 at 10:47

marc_s · Accepted Answer · 2011-02-06 10:49:22Z

2

You should use the HAVING clause only to limit on something that's been aggregated in your query above - e.g. if you want to select all those rows where a SUM(....) or COUNT(...) is larger than say 5, then you'd use HAVING SUM(...) > 5

What you're doing here is a standard WHERE clause - add it there!

SELECT req_ownerid, count(req_status_lender) AS total6 
FROM bor_requests
WHERE req_ownerid = 70
      AND ((req_status_lender = 0 AND req_status_borrower = 0) OR 
           (req_status_lender = 1 AND req_status_borrower = 1))
GROUP BY req_ownerid

answered Feb 6, 2011 at 10:49

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Counting Records with Count and Having

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related