Execute count(*) on a group-by result-set

问题:

I am trying to do a nice SQL statement inside a stored procedure.

I looked at the issue of seeing the number of days that events happened between two dates. My example is sales orders: for this month, how many days did we have sales orders?

Suppose this setup:

CREATE TABLE  `sandbox`.`orders` (
  `year` int,
  `month` int,
  `day` int,
  `desc` varchar(255) 
) 

INSERT INTO orders (year, month, day, desc)  
VALUES (2009,1,1, 'New Years Resolution 1')
      ,(2009,1,1, 'Promise lose weight')
      ,(2009,1,2, 'Bagel')
      ,(2009,1,12, 'Coffee to go')

For this in-data the result should be 3, since there has been three days with sale. The best solution I found is as below.

However, making a temporary table, counting that then dropping it seemes excess. It "should" be possible in one statement.

Anyone who got a "nicer" solution then me?

/L

SELECT [Year], [Month], [Day]
INTO #Some_Days
FROM Quarter
WHERE Start >= '2009-01-01' AND [End] < '2009-01-16'
GROUP BY [Year], [Month], [Day]

SELECT count(*) from #Some_Days

回答1:

Apologies if I'm misunderstanding the question, but perhaps you could do something like this, as an option:

SELECT COUNT(*) FROM
    (SELECT DISTINCT(SomeColumn)
       FROM MyTable
      WHERE Something BETWEEN 100 AND 500
      GROUP BY SomeColumn) MyTable

... to get around the temp-table creation and disposal?

回答2:

There are two basic options which I can see. One is to group everything up in a sub query, then count those distinct rows (Christian Nunciato's answer). The second is to combine the multiple fields and count distinct values of that combined value.

In this case, the following formula coverts the three fields into a single datetime.

DATEADD(YEAR, [Quarter].Year, DATEADD(MONTH, [Quarter].Month, DATEADD(DAY, [Quarter].DAY, 0), 0), 0)

Thus, COUNT(DISTINCT [formula]) will give the answer you need.

SELECT
    COUNT(DISTINCT DATEADD(YEAR, [Quarter].Year, DATEADD(MONTH, [Quarter].Month, DATEADD(DAY, [Quarter].DAY, 0), 0), 0))
FROM
    Quarter
WHERE
    [Quarter].Start >= '2009-01-01'
    AND [Quarter].End < '2009-01-16'

I usually use the sub query route, but depending on what you're doing, indexes, size of table, simplicity of the formula, etc, this Can be faster...

Dems.

回答3:

How about:

SELECT COUNT(DISTINCT day) FROM orders 
WHERE (year, month) = (2009, 1);

Actually, I don't know if TSQL supports tuple comparisons, but you get the idea.

COUNT(DISTINCT expr) is standard SQL and should work everywhere.

回答4:

You should use nested Select statements. Inner one should contain group by clause, and the outer one should count it. I think "Christian Nunciato" helped you already.

Select Count(1) As Quantity
From
(    
    SELECT [Year], [Month], [Day]
    INTO #Some_Days
    FROM Quarter
    WHERE Start >= '2009-01-01' AND [End] < '2009-01-16'
    GROUP BY [Year], [Month], [Day]
) AS InnerResultSet

回答5:

SELECT [Year], [Month], [Day]
FROM Quarter
WHERE Start >= '2009-01-01' AND [End] < '2009-01-16'
GROUP BY [Year], [Month], [Day]
COMPUTE COUNT(*)