Counting distinct records grouped by date

[yuhuibc]

I have a table with 2 fields, "fubar" and "date", in an Access database. "date" stores the dates in the full date format, i.e. date and time, e.g. 2/20/2004 1:34:53PM.

"fubar" contains values that may or may not be unique, e.g. the values could be:
12345
67890
12345
74653
00987
12345
...

What I need to do is to get a count of unique fubar values grouped by dates. However, I need to group only by the dates, not the time.

So given the following table:
fubar date
12345 2/20/2004 1:34:53PM
67890 2/20/2004 5:37:23PM
12345 2/20/2004 10:19:47PM
74653 2/19/2004 12:38:30AM
00987 2/19/2004 7:58:29AM
12345 2/18/2004 3:14:35PM

the SQL statement should return

date count of distinct fubar's
2/20/2004 2
2/19/2004 2
2/18/2004 1

Currently, I use multiple SQL statements, one for each date. Each SQL statement is:

"SELECT COUNT(fubar) FROM (SELECT DISTINCT fubar FROM table WHERE DATEPART('yyyy', date) = " & someyear & " AND DATEPART('m', date) = " & somemonth & " AND DATEPART('d', date) = " & someday & ")"

(I specify someday, somemonth and someyear in my VBScript code.)

But I'd like to be able to group everything into one SQL statement so that I only have to deal with one list of records. Is this possible?

 

[JarlH]

SELECT da,COUNT(DISTINCT fubar) FROM
(SELECT CAST(d AS DATE) da,fubar FROM t)
GROUP BY da;


This is ANSI/ISO SQL. Don't know if it works on Access...

Besides DATE is a reseved word, so I renamed that column to d. And the table name to t.

 

[yuhuibc]

Unfortunately, Access doesn't recognize both "CAST(variable_name)" and "COUNT(DISTINCT variable_name)".

Oh, and I simplified my variable names to "date" and "table" for this post.

 

[JarlH]

Alright, what you've got to is to extract the specfic date from your column. Try something like:

SELECT da, COUNT(DISTINCT fubar) FROM
(SELECT DATEPART('yyyy', date)*10000
+ DATEPART('mm', date)*100
+ DATEPART('dd', date) AS da,
fubar FROM t)
GROUP BY da;

This may work if DATEPART returns an integer value. Otherwise you could try SUBSTRING to get the date out of your column.

 

[Golom]

Haven't tested so you may need to mess with the syntax

Select Format(DateField, "yyyy/mm/dd") As [TheDate], Count(*) As [fubarCount]
From   tbl
Group By Format(DateField, "yyyy/mm/dd"), fubar

Earlier versions of Access (97 and below) don't support "Count(DISTINCT ...)".

 

[r937]

Golom, it Access you need a query-of-query, simply to get the "count distinct" aspect working

distinct_q:

[tt]select Format(DateField, "yyyy/mm/dd&quot As [TheDate]
, fubar
from tbl
group
by Format(DateField, "yyyy/mm/dd&quot
, fubar[/tt]

then

[tt]select TheDate
, count(*) as [distinctfubars]
from distinct_q
group
by TheDate[/tt]




rudy

http://r937.com/

SQL Consulting

 

[Golom]

r937
'tis true ... the effect can be achieved that way ... just not the specific syntax. Good catch Rudy.