SQL使用Count和Hing来统计记录

发布于 2024-10-16 05:52:24 字数 566 浏览 5 评论 0原文

我对我认为是一个简单的记录计数查询有问题：

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'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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

固执像三岁 2024-10-23 05:52:24

您应该仅使用 HAVING 子句来限制上面查询中聚合的内容 - 例如，如果您想要选择 SUM(....) 或 COUNT(...) 大于 5，那么您可以使用 HAVING SUM(...) > 5

您在这里所做的是一个标准的 WHERE 子句 - 将其添加到那里！

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

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

回复收藏 0 原文

~没有更多了~