How do I do COUNT(*) with a group by clause in db2

2019-07-24 03:58发布

问题:

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?

回答1:

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