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

Question

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

Answer 1

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.

Answer 2

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?

Answer 3

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

Answer 4

Bill, close, but this only works when I desire a set of days within the same month. I a need sales-days beteen 30:th December and 2:nd January, then I cannot use that trick.

I mean, COUNT only allows one argument. I would like to select on several arguments. You cannot say COUNT(DISCTINCT (year,month,day) ), right?

Really appreciate the input!

Answer 5

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.