Count day in table

[lsy]

How can i get the count of date inside a database. the date might be duplicate, so i would like to know how many days are in the table.
i try in this way...

SELECT count(distinct( format([mydate],"dd/mm/yyyy"))) as DayCount from tbl

but error message "undefined function 'distinct' in expression"
Help needed...

 

[Dennisk]

Select Distinct Count(....)

 

[lsy]

Select Distinct Count(....)

it does not group in distinct. it return the number of all record. if my table have 22 record, it return 22.
what i want is distinct out the date and count the record.

 

[namliam]

I dont think Access supports this (oracle does)

Try a subselect:

select count(day) from (SELECT distinct( format([mydate],"dd/mm/yyyy")) as Day from tbl)

 

[Dennisk]

if you want to group then use that keyword instead of distinct ans I suspect you are confusing the two.

 

[lsy]

I dont think Access supports this (oracle does)

Try a subselect:

select count(day) from (SELECT distinct( format([mydate],"dd/mm/yyyy")) as Day from tbl)

ya got it... but actually this is just part of it, when i apply this to my query, other record might not be able to query out..

my table consist of start_date, end_date, NumberOfFail... what i need is i need to know got how many days in the record, get the total of NumberOfFail, and get the interval between start_date and stop_date.
any idea how can i did that?

 

[namliam]

Yep use the Group by...

What you do is something like so...
select day, count(day), otherfieldyouneed, etc from (SELECT format([mydate],"dd/mm/yyyy") as Day, sum(yourfield) as total, count(total) as totals, etc.... from tbl
group by format([mydate],"dd/mm/yyyy") )
group by day