SQL 如何使用 db2 中的 group by 子句执行 COUNT(*)？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5247378/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I do COUNT(*) with a group by clause in db2?
提问by Mike
I want to know how many rows are going to be returned by this query:
我想知道此查询将返回多少行：
SELECT
MONTH(DHSTMP), SUM(DHDLDY), SUM(DHBUDS), YEAR(DHSTMP)
FROM
DSHDAY
WHERE
DHSTMP BETWEEN '2009-07-26 00:00:00' AND '2011-03-09 23:59:59'
GROUP BY
YEAR(DHSTMP), MONTH(DHSTMP) ORDER BY YEAR(DHSTMP), MONTH(DHSTMP)
So I tried:
所以我试过：
SELECT
COUNT(*)
FROM
DSHDAY
WHERE
DHSTMP BETWEEN '2009-07-26 00:00:00' AND '2011-03-09 23:59:59'
GROUP BY
YEAR(DHSTMP), MONTH(DHSTMP)
But it tells me how many rows for each group.
但它告诉我每组有多少行。
How can I get the number of rows that will be returned for the first query?
如何获得第一次查询将返回的行数？
回答by bw_üezi
This should work. but it's not performant.
(if you're only interested in the count you probably don't want to calculate sum aggregation and ordering)
这应该有效。但它没有性能。
（如果您只对计数感兴趣，您可能不想计算总和聚合和排序）
SELECT COUNT(*)
FROM (
SELECT
MONTH(DHSTMP),
SUM(DHDLDY),
SUM(DHBUDS),
YEAR(DHSTMP)
FROM DSHDAY
WHERE DHSTMP BETWEEN '2009-07-26 00:00:00' AND '2011-03-09 23:59:59'
GROUP BY
YEAR(DHSTMP),
MONTH(DHSTMP)
ORDER BY
YEAR(DHSTMP),
MONTH(DHSTMP)
) i
Try this
尝试这个
SELECT COUNT( DISTINCT i.month_year )
FROM (
SELECT
CONCAT( MONTH(DHSTMP), YEAR(DHSTMP) ) AS month_year
FROM DSHDAY
WHERE DHSTMP BETWEEN '2009-07-26 00:00:00' AND '2011-03-09 23:59:59'
) i
Variation (in answer to Mike's comment)
变化（回应迈克的评论）
SELECT COUNT(*)
FROM (
SELECT 0
FROM DSHDAY
WHERE DHSTMP BETWEEN '2009-07-26 00:00:00' AND '2011-03-09 23:59:59'
GROUP BY
YEAR(DHSTMP),
MONTH(DHSTMP)
) i
