Distinct Count

[pullingteeth]

I know Access can't do:

select count(distinct blah) from ...

Is there any workaround APART from creating a query that just does:

select distinct blah from...

and then another which counts the results from the prior query? I.e., is there an all in one SQL solution to this problem?

Thanks!

 

[scriverb]

something like this might work for you.

Select Count(A.[fieldname]) as AliasName 
from [i]tablename[/i] as A 
WHERE A.[ID] IN (Select Distinct B.[ID] FROM [i]tablename[/i] as B WHERE [I]expression . . [/i]);

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[pullingteeth]

Thanks; I have to make this question more complex, now. The ID field in question is a timestamp which is a general date (4/5/2004 19:17 or whatever); I am interested only in the number of distinct days.

If I do

select distinct int(timestamp) from book_event

...I get 12 rows.

However, if I do:

Select Count(int(timestamp)) as AliasName 
from book_event
WHERE int(timestamp) IN (Select Distinct int(timestamp) FROM book_event);

...I get "45" as an answer, because all of the int(timestamps) of course are present in the results from the subquery. I don't think this would be changed using the aliasing (which didn't work with the int() function).

 

[scriverb]

Try this:

Select Count(Format([timestamp], "mm/dd/yyyy")) as AliasName, Format([timestamp], "mm/dd/yyyy") as DayOfCount
from book_event
WHERE Format([timestamp], "mm/dd/yyyy") IN (Select Distinct Format([timestamp], "mm/dd/yyyy") FROM book_event);

See if this rolls everything up for you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[pullingteeth]

Thanks --

Hmmm, it's complaining about <I>Format([timestamp], "mm/dd/yyyy")</I> not being part of an aggregate function; when I try to <I>group by Format([timestamp], "mm/dd/yyyy")</I> it doesn't have the desired effect (it gives me the count per date).

 

[scriverb]

Post back all of your SQL so I can take a look. I have it working here. You see the problem is that you both date and time stored in this field. What I did by formatting everything to mm/dd/yyyy was change everything to a specific day. Works here with all of these exact changes.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[pullingteeth]

Hi, it's an exact paste of your solution. Are you using Access 97 or 2K? (I'm stuck using 97, for now.)

I'm not convinced that your query (in theory) is what I need, though. Add this data to your table:

12/04/04 12:34
12/04/04 12:35
12/04/04 12:36
12/05/04 12:34
12/05/04 12:35
12/05/04 12:36

When you run your query, doesn't it give you:

3, 12/04/04
3, 12/05/04

What I actually need it to do is give me:

2

(Since if I am to just count the number of records, I can do that with a non-subquery version)

 

[scriverb]

Sorry about that but you original posting was pretty vague. I went too far and gave you a count by Date. Here is one that counts total unique dates.

Select Format([timestamp], "mm/dd/yyyy") as AliasName 
from book_event
ORDER BY Format([timestamp], "mm/dd/yyyy");

Now use the DCount function to retrieve your count:

Dim x as Long
x = DCount("*", "[i][red]yourqueryname[/red][/i]")

This should give you your figure.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[pullingteeth]

Thanks, DCount is good to know about (I'd rolled my own previously.) This still is a two-stepper though, and for this purpose:

select distinct int(timestamp) from book_event

...works just as well, and is more concise, right? (Although it doesn't pull back what the dates actually are, as yours does.) Is int() or Format any more or less SQL92 compliant, do you know?

 

[scriverb]

I don't know about SQL92 compliant but they are both ACCESS functions so if they are available in ACCESS then your SQL92 should be able to call them. As for the SQl we both provided you still have to be able to get the value out of your query SQL. So, if we figured out a way to come up with a single row with a single field you would still need to use a function like DLookup to get the value out. So yes, a double-step but I don't see the downside of it being that way.

Good luck.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[pullingteeth]

The downside as I see it is performance based. If I were to somehow get the Jet DB engine to give me a count (using SQL) I'd be able to retrieve it as:

dim rs as Recordset
dim count as Integer

set rs = currentdb.openrecordset(getCount(), dbopensnapshot)
count = rs!countOfBookDays

...

function getCount() as String
    dim x as String
    x = "SELECT count(distinct ...) as countOfBookDays ..."
    getCount = x
end function

I say that this would be a performance boost over DCount or iterating over RecordSet to count it because the theory behind relational databases is that once you define the query for them, they come up with an optimum method to generate it. I've never done any performance tuning on Access, but when I've worked on SQL Server and Oracle in the past, we would spend time sometimes removing 10ms here and 10ms there; often a great gain was made by writing better SQL, and giving the database more to do than the code. (While 10ms here and there doesn't matter for a desktop single-person application, if you have 10s of thousands of users and millions of records, it quickly adds up.)

But oh well, seems like it can't be done in this case (and I guess for my application it doesn't really matter at the end of the day). Thanks for your help!