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.
精彩评论