|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
How can I GROUP by MONTH using a date variable?
How do I group by MONTH in a query using a DATE variable?
I want to count the number of entries in a table based on # of events per month. If I use the following code, it groups by DAY (using the entire date) and thus I get # of events per DAY: SELECT WORK.Date, Count(WORK.Event) FROM WORK GROUP BY WORK.Date, WORK.Event; Since I'm using Access, I can implement the following code: SELECT WORK.Event, Format(WORK.Date, "yy/mm") AS [Month], Count(Event) FROM [WORK] GROUP BY WORK.Event, Format(WORK.Date, "yy/mm"); But this is executable ONLY on Access, and I'd rather implement a pure SQL solution, not using the VB function 'Format' if possible, and I'm sure there must be a way. Can anyone help? |
|
#2
|
|||
|
|||
|
RE: How can I GROUP by MONTH using a date variable?
Quote:
Try to use YEAR and MONTH SQL function (e.g. SELECT work.event, year(work.date) as year, month(work.date) as month, count(work.event) as noofevents from work group by work.event, year, month ) |
|
#3
|
|||
|
|||
|
RE: How can I GROUP by MONTH using a date variable?
try this:
SELECT * FROM WORK WHERE (((Year(Date))=2002) AND ((Month(DATE))=11)); This should help you to solve the problem... |
![]() |
| Viewing: Codewalkers Forums > Other Technologies > Database Help > How can I GROUP by MONTH using a date variable? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|
|
|