[SOLVED] Count the Week, Month, Quarter, and Year

[jrm]

I want to be able to count the "Violations" per week, month, quarter and year.  I have made this work in Access, different syntax, but have ported everything to MySQL. Can somebody tell me where my error is at.  I have gone thru most of the functions for date math in MySQL and still no answer.

 

Query:

SELECT Count(Violations.VioltId) AS CountOfVioltId, Date_Format(DTGoViol,%X) AS NumofWk, DATE_SUB(DATE(),INTERVAL DATE(%w) DAY) AS "WeekStart"

FROM Violations GROUP BY Format(DTGoViol,%X), DATE_ADD(CURRENT_DATE(),- Interval DAYOFWEEK(CURRENT_DATE()) Day)

HAVING DATE_Format(DTGoViol,%X)=DATE_Format(CURRENT_DATE(),%X);

 

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%V) AS "NumofWk", DATE_SUB(DATE(),INTERVAL DATE(%w) DAY) AS "WeekStart"

FROM Vi' at line 1

 

Table:

'Violations', 'CREATE TABLE `Violations` (

 `VioltId` int(10) NOT NULL auto_increment,

 `CadetID` int(10) default NULL,

 `StaffId` int(10) default NULL,

 `DTGoViol` datetime default NULL,

 `Location` varchar(50) character set latin1 collate latin1_bin default NULL,

 `Description` longtext,

 `Sanction_Recommendation` longtext,

 `DTGNot` datetime default NULL,

 `Remarks` longtext,

 `PltSgtNum` int(10) default NULL,

 `TimeStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

 PRIMARY KEY  (`VioltId`),

 KEY `CadetID` (`CadetID`),

 KEY `IncidentId` (`VioltId`),

 KEY `IncidentStaffId` (`StaffId`),

 KEY `PltSgtNum` (`PltSgtNum`)

) ENGINE=MyISAM AUTO_INCREMENT=140 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC'

 

 

[fenway]

Missing quotes for those format specifiers.

[jrm]

Thank you.  It seems that when you are in a rush, the simple things are missed. 

 

Plus, I have simplified the query to:

 

SELECT Count(Violations.VioltId) AS CountOfVioltId

FROM Violations_be.Violations

WHERE Date_Format(date(DTGoViol),"%V")=Date_Format(CurDate(),"%V");