开发者

Fast SQL question about multiple month dates

SELECT DISTINCT MonthName(Month([Date])) AS [Month], tblSupportCalls.System, Count(tblSupportCalls.System) AS [Total for System], Year([Date]) AS [Year]
FROM tblSupportCalls
WHERE (((tblSupportCalls.Date) Between Now() And Now()-7) AND ((tblSupportCalls.System) In ('Career Campus','E-PEEP')) AND ((tblSupportCalls.StartTime) Is Not Null) AND ((Hour([StartTime]))>=7))
GROUP BY tblSupportCalls.System, tblSupportCalls.Date;

Whenever I input that it gives me multiple months like:

July
July
July

I want it just to say July just 1 time and I cant figure out why ever field is repeating itself for different days. I just want it to say:

MONTH | System | Total Systems | Year

what I am looking at is

> MONTH | System | Total Systems | Year
> July      CC           2         2010
> July      CC           7         2010
> July      CC           9         2010
> July      EE           1         2010
> July      EE           2         2010

Needs to be:

MONTH 开发者_如何学JAVA| System | Total Systems | Year
July      CC          18         2010
July      EE          03         2010


You'd want to group by your Year and Month instead of tblSupportCalls.Date.

...
GROUP BY tblSupportCalls.System, Year([Date]), Month([Date]);


You're still grouping the results by date, though (not by month). I think what you want is something like this:

GROUP BY tblSupportCalls.System, Year ([tblSupportCalls.Date]), [Month([tblSupportCalls.Date]);

I'm thinking you still want to group by Year as well, so that July 2010 shows up in a different row from July 2011.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜