date() in sql statements

[brown2005]

if i have

$sql = "SELECT * FROM table1 GROUP BY year";

but i only have a date field in my table, so how can i get just the year from it

[master82]

WHERE year = $year

Is that what you mean?

[brown2005]

no i want to group by year so it is like

2002 - 5
2004 - 3
2006 - 3

and so on

so it counts how many records there are for each year

[HuggieBear]

What database are you using?  MySQL, Oracle, MSSQL?

Rich

[brown2005]

MySQL

[brown2005]

..

[HuggieBear]

OK, it shouldn't be too hard...

What datatype is your date column?  Date, Timestamp, Datetime?

Rich

[brown2005]

date

[HuggieBear]

OK, give this a try...

[code]
SELECT year(date_column), count(*)
FROM table_name
GROUP BY year(date_column)
[/code]

Regards
Rich

[brown2005]

spot on rich.

thanks very much

Regards

Rich (lol)

[brown2005]

one more thing, before i leave u alone, lol, if say i use month(), obviously it prints out 9 as the numeric month, but how can i print out September instead

[HuggieBear]

Glad it worked, taught myself something new there too.  I'm used to working in Oracle, so I took the following oracle code...

[code]
SELECT to_char(date_column, 'YYYY') "Date", count(*) "Count"
FROM table_name
GROUP BY to_char(date_column, 'YYYY')
ORDER BY "Date" DESC
[/code]

... and then worked out what the equivalent functions were in MySQL.

I've posted this here for anyone who comes across your post and wonders how to do it in Oracle.

Rich

[HuggieBear]

[quote]
one more thing, before i leave u alone, lol, if say i use month(), obviously it prints out 9 as the numeric month, but how can i print out September instead
[/quote]

Try monthname() instead of month()

Rich

[brown2005]

nice one, is there a list of the functions like the php ones on phpfreaks.com

[HuggieBear]

[quote]
Is there a list of the functions like the php ones on phpfreaks.com
[/quote]

I just went to the [url=http://dev.mysql.com/doc/refman/5.0/en/]MySQL site[/url].

[brown2005]

ok cool

thanks for all ur help