SQL计数具有计数和拥有的记录 [英] SQL Counting Records with Count and Having

问题描述

我遇到问题，我认为是一个简单的查询计数记录：

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

我认为这会计算所有记录，其中（req_status_lender = 0 AND req_status_borrower = 0） （req_status_lender = 1 AND req_status_borrower = 1），然后给出总数，但它只给出了（req_status_lender = 0 AND req_status_borrower = 0）或（req_status_lender = 1 AND req_status_borrower = 1）的总数。

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?

推荐答案

应该使用 HAVING 子句来限制在上面的查询中聚合的内容 - 例如如果您要选择 SUM（....）或 COUNT（...）大于5，那么你会使用 HAVING SUM（...）> 5

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

你在这里做的是一个标准的 WHERE

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 

这篇关于SQL计数具有计数和拥有的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！