I do have a database with a table as below:
CREATE TABLE `error_monitoring` (
`id` int(11) NOT NULL,
`error_monitoring_page` varchar(256) NOT NULL,
`error_code` varchar(64) NOT NULL,
`monitoring_country` varchar(64) DEFAULT NULL,
`platform` varchar(64) NOT NULL,
`total` int(64) NOT NULL,
`visits` int(64) NOT NULL,
`unique_visitors` int(64) NOT NULL,
`date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
In this table is stared data that comes from a .csv file. The .csv fila has only 5 columns:
- unique visitors
The rest of the information like country and platform is taken from the and error_code from the field "error_monitoring_page" in which this information is contained and then added into the corresponding field.
In the .csv file the information comes as below example:
This field "us:es:website:track-transfer:moneytransfer-tab|C1620" is containing :
error code : last 5 characters os the string.
monitoring_country: first 2 characters of the string.
and the platform: website
The .csv file is received every hours some we have in the database data for every hour of the day for each error_monitoring_page.
I need an MySql query that extracts and calculate the error codes that is exceeding for the total count of the day the Upper Control Limit.
UCL = average+(2*standard deviation)
if total count of the current day for the error code C1620 is higher that the UCL the query should extract all the information of this error code:
- Error code
- standard deviation
- total of the day
The average and the standard deviation should be calculated based on the total by day for the last 30 days.
Been working for several years on MySQL queries and having a great knowledge of MySQL queries. Relevant Skills and Experience MySQL, SQL, PHP Proposed Milestones $22 USD - Understanding and writing optimized query