count dates, query help 2

[ethorn10]

Ok, hopefully I'll make some sense of this so it won't take long to figure out. I'm just stumped...

Have a field (date_applied) that is a timestamp. I format the date with date_format and %m/%e/%Y...I want to count all occurrences of any date. So let's say you have this:

3/12/2005
3/28/2005
3/28/2005
4/1/2005
4/1/2005
4/1/2005
4/11/2005

I'd like to see this:

count | date_applied
1 | 3/12/2005
2 | 3/28/2005
3 | 4/1/2005
1 | 4/11/2005

Any help would be appreciated.

 

[steven290]

try
select count(*) as count, date_applied
from tblname
group by date_applied

 

[ethorn10]

to be fair, i should have shown what i've already tried. and that was one of the queries i came up with. the other was to use date_format to get only the date portion of the stamp so that it would count matching dates (there'd be no matching dates with the rest of the stamp (crazy time stamps)).

it looked something like this:

select date_format(date_applied,'%m/%e/%Y'), count(date_format(date_applied,'%m/%e/%Y')) as count 
from tblname
group by date_applied

still no dice.

 

[steven290]

how about

select date_format(date_applied,'%m/%e/%Y'), count(*) as count
from tblname
group by date_format(date_applied,'%m/%e/%Y')

 

[ethorn10]

bingo. i knew i was close but i just couldn't piece it together. many thanks steven.

 

[steven290]

no prob

 

[TonyGroves]

You could also use:
[tt]
SELECT DATE_FORMAT(date_applied,'%c/%e/%Y'), count(*) `count`
FROM tblname
GROUP BY date_applied
[/tt]
which would be simpler and faster.

 

[steven290]

if you say so, just pullin from my head so no way to test

 

[ethorn10]

actually, tony, your version doesn't quite do the trick. for some reason it still shows each date over and over with 1 under count instead of showing the date once and 2 or 3 or 4 under count.

but i do appreciate all the quick replies. i should have known you guys would help out fast.

 

[steven290]

that would make more sense cause your need to group the formatted field not the way the field really is

 

[TonyGroves]

Hmm ... it works for me:
[tt]
SELECT DATE_FORMAT(d,'%c/%e/%Y'), count(*) `count`
FROM t
GROUP BY d
[/tt]
produces:[tt]
+---------------------------+-------+
| DATE_FORMAT(d,'%c/%e/%Y') | count |
+---------------------------+-------+
| 3/12/2005 | 1 |
| 3/28/2005 | 2 |
| 4/1/2005 | 3 |
| 4/11/2005 | 1 |
+---------------------------+-------+
[/tt]

 

[ethorn10]

i don't know then...i tried it again. maybe because i'm adding a where clause that i didn't mention? when i add the where clause to steven's it still works...but not with yours tony.

SELECT DATE_FORMAT(date_applied,'%c/%e/%Y'), count(*) `count`
FROM tblname where valid=1 
GROUP BY date_applied

like i said...i know i didn't mention it from the start but it DOES work with steven's. i'm rather intrigued by this now...

 

[steven290]

like i said before- if you say so. lol

 

[TonyGroves]

Sorry, my mistake. The field is a timestamp, not a date, so the time part must be stripped before grouping. It could be a zillion times faster if you didn't have to do that, but you've no choice here.

 

[ethorn10]

no problem. i appreciate your input regardless.

 

[ethorn10]

Ok guys -- I want to expand just slightly on the query already working above. Just to reiterate, the working query is:

select date_format(date_applied,'%M') as date_applied, count(*) as count
from tblname
group by date_format(date_applied,'%M')

Now where I'm wanting to take this is..say..I want to know who "applied" for all of the different options. So a result set similar to this:

date_applied  |  count   |   item
 January      |    7     |  oranges
 January      |    3     |  apples
 February     |   10     |  oranges
 February     |   15     |  apples

and so on. also, item is in another table (tblname2) and they are joined on id (an int).

I hope to hear that this can be accomplished. I'm pretty sure it can, I've just officially lost my mind...it's Friday.

 

[TonyGroves]

You could just expand your query slightly:
[tt]
SELECT
DATE_FORMAT(date_applied,'%M') date_applied,
COUNT(*) count,
item
FROM tblname
GROUP BY DATE_FORMAT(date_applied,'%M'),item
[/tt]

 

[ethorn10]

Like I said...pretty sure it could be done and you guys never cease to amaze me. I think I messed with every variation of that and nothing was working for me. The 2nd set of eyes known as tek-tips always finds the fix though.

Thanks guys.