SQL syntax (Newbie Question)

Dear Group,

I have a table (Members) which has one field (for arguement's sake) which is
a datetime field called JoinDate which stores various dates.

I would like to create a SQL statement that could give me the aggregated
result:

August : 3
July : 45
June: 84

It is a total for each month. I would assume that I would use the DatePart
function to group and then count and specify that I only want the last three
months data but I am unsure of how to do this exactly.

Any hints would be appreciated. Thanks again.

Alastair MacFarlane

Select DatePart(mm, JoinDate), Count(DatePart(mm, JoinDate)) FromMembers Group

Select DatePart(mm, JoinDate), Count(DatePart(mm, JoinDate)) From
Members Group By DatePart(mm, JoinDate)

Hope I understood the problem correct. This query may help...


-ArunDhaJ

SQL syntax (Newbie Question)

You can use the DATENAME function if you need to get results as
posted:

SELECT DATENAME(MONTH, JoinDate) AS join_month,
COUNT(*) AS cnt
FROM Members
GROUP BY DATENAME(MONTH, JoinDate);

However, this may not give you the correct results if the data spans
over multiple years. You can include the year in the grouping:

SELECT CONVERT(CHAR(7), JoinDate, 126) AS join_year_month,
COUNT(*) AS cnt
FROM Members
GROUP BY CONVERT(CHAR(7), JoinDate, 126);


Plamen Ratchev
http://www.SQLStudio.com

ArunDhaJThanks for the speedy reply.

ArunDhaJ

Thanks for the speedy reply.

How could I specify for the last three months including the month we are
currently in? I presume this would be the WHERE clause.

Thanks again.

Alastair

SQL syntax (Newbie Question)

I forgot to include the conditions for the last 3 months:

SELECT DATENAME(MONTH, JoinDate) AS join_month,
COUNT(*) AS cnt
FROM Members
WHERE JoinDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP)
- 2,
0)
AND JoinDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP)
+ 1,
0)
GROUP BY DATENAME(MONTH, JoinDate);

SELECT CONVERT(CHAR(7), JoinDate, 126) AS join_year_month,
COUNT(*) AS cnt
FROM Members
WHERE JoinDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP)
- 2,
0)
AND JoinDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP)
+ 1,
0)
GROUP BY CONVERT(CHAR(7), JoinDate, 126);


Plamen Ratchev
http://www.SQLStudio.com