OK, many thanks for your help and suggestion.
I try this query for calculation of a '90th percentile':
SELECT SUM(g1.r) sr,
g2.O2_PPM l,
SUM(g1.r)/(SELECT COUNT(*) FROM listfiles) p
FROM (SELECT COUNT(*) r, O2_PPM
FROM listfiles GROUP BY O2_PPM) g1
JOIN (SELECT COUNT(*) r, O2_PPM FROM listfiles GROUP BY O2_PPM) g2
ON g1.O2_PPM < g2.O2_PPM
GROUP BY g2.O2_PPM
HAVING p > 0.9
ORDER BY p
LIMIT 1
And the ouptus is:
sr l p
46 27787 0,92
I need calculating the 90th percentile in my table mysql for this columns:
O2_PPM, N2_PPM, CO_PPM, CO2_PPM, H2_PPM, CH4_PPM, C2H6_PPM, C2H4_PPM, C2H2_PPM, C2H2_C2H4_PPM, CH4_H2_PPM, C2H4_C2H6_PPM, H20_PPM
I need 13 differents queries for calculating the '90th percentile' ?
And I have another problem:
If I use Microsoft excel for calculation of a '90th percentile' the output for the column O2_PPM is 22559,5... in mysql 27787, what's the difference?
My Table MySQL:
# MySQL-Front 5.0 (Build 1.183)
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */;
/*!40101 SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;
/*!40103 SET SQL_NOTES='ON' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS */;
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
# Host: localhost Database: cm714
# ------------------------------------------------------
# Server version 5.1.44-community
USE `cm714`;
#
# Table Objects for table listfiles
#
DROP TABLE IF EXISTS `listfiles`;
CREATE TABLE `listfiles` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`O2_PPM` decimal(10,2) DEFAULT NULL,
`N2_PPM` decimal(10,2) DEFAULT NULL,
`CO_PPM` decimal(10,2) DEFAULT NULL,
`CO2_PPM` decimal(10,2) DEFAULT NULL,
`H2_PPM` decimal(10,2) DEFAULT NULL,
`CH4_PPM` decimal(10,2) DEFAULT NULL,
`C2H6_PPM` decimal(10,2) DEFAULT NULL,
`C2H4_PPM` decimal(10,2) DEFAULT NULL,
`C2H2_PPM` decimal(10,2) DEFAULT NULL,
`C2H2_C2H4_PPM` decimal(10,2) DEFAULT NULL,
`CH4_H2_PPM` decimal(10,2) DEFAULT NULL,
`C2H4_C2H6_PPM` decimal(10,2) DEFAULT NULL,
`H20_PPM` decimal(10,2) DEFAULT NULL,
`sSTATE` varchar(1) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=5912 DEFAULT CHARSET=latin1;
#
# Dumping data for table listfiles
#
LOCK TABLES `listfiles` WRITE;
/*!40000 ALTER TABLE `listfiles` DISABLE KEYS */;
INSERT INTO `listfiles` VALUES (1,11477,65991,185,2232,9,5,2,3,1,0,1,1,8,'E');
INSERT INTO `listfiles` VALUES (2,2253,73744,345,2399,14,13,5,6,1,0,1,1,8,'E');
INSERT INTO `listfiles` VALUES (3,3749,71690,602,3844,28,9,2,6,1,0,0,3,10,'E');
INSERT INTO `listfiles` VALUES (4,7418,63711,404,7686,42,32,11,45,13,0,1,4,14,'E');
INSERT INTO `listfiles` VALUES (5,6167,61773,366,6332,80,5,7,0,2,0,0,0,43,'E');
INSERT INTO `listfiles` VALUES (6,22400,65764,278,2613,27,4,2,2,0,0,0,1,23,'E');
INSERT INTO `listfiles` VALUES (7,13338,58453,310,2963,15,14,10,6,0,0,1,1,10,'E');
INSERT INTO `listfiles` VALUES (8,224,56688,204,1813,86,13,18,15,24,2,0,1,8,'E');
INSERT INTO `listfiles` VALUES (9,1477,63212,705,4170,132,46,17,42,76,2,0,2,8,'E');
INSERT INTO `listfiles` VALUES (10,27889,64781,49,371,6,2,3,0,6,0,0,0,0,'E');
INSERT INTO `listfiles` VALUES (11,21912,61208,99,460,10,5,15,4,0,0,0,0,0,'E');
INSERT INTO `listfiles` VALUES (12,365,58779,351,3656,61,47,51,6,0,0,1,0,25,'E');
INSERT INTO `listfiles` VALUES (13,5625,71446,1047,9944,30,15,7,22,32,1,0,3,23,'E');
INSERT INTO `listfiles` VALUES (14,23995,67130,167,2714,19,5,3,6,32,5,0,2,4,'E');
INSERT INTO `listfiles` VALUES (15,14503,66305,287,7298,62,12,26,11,0,0,0,0,14,'E');
INSERT INTO `listfiles` VALUES (16,15688,72239,449,4922,63,21,6,49,310,6,0,8,6,'E');
INSERT INTO `listfiles` VALUES (17,571,65256,703,4403,253,48,23,49,179,4,0,2,16,'E');
INSERT INTO `listfiles` VALUES (18,446,61688,691,3879,31,16,5,8,1,0,1,2,10,'E');
INSERT INTO `listfiles` VALUES (19,31998,67458,34,774,4,2,1,1,1,1,0,1,9,'E');
INSERT INTO `listfiles` VALUES (20,30852,66030,44,846,4,2,1,1,1,1,0,1,8,'E');
INSERT INTO `listfiles` VALUES (21,17365,50478,15,453,1,1,1,1,1,1,1,1,0,'E');
INSERT INTO `listfiles` VALUES (22,27787,71413,187,1526,4,2,1,1,1,1,0,1,5,'E');
INSERT INTO `listfiles` VALUES (23,17099,73367,271,1746,19,2,5,1,1,1,0,0,13,'E');
INSERT INTO `listfiles` VALUES (24,418,63638,668,3858,115,53,27,45,35,1,0,2,23,'E');
INSERT INTO `listfiles` VALUES (25,5581,66242,511,3504,90,7,3,13,39,3,0,4,13,'E');
INSERT INTO `listfiles` VALUES (26,432,65477,491,2781,38,11,4,6,2,0,0,1,16,'E');
INSERT INTO `listfiles` VALUES (27,5799,68110,438,3715,20,10,7,7,0,0,0,1,16,'E');
INSERT INTO `listfiles` VALUES (28,7472,61134,428,5331,112,2,3,1,16,16,0,0,26,'E');
INSERT INTO `listfiles` VALUES (29,16642,52221,108,1913,1319,62,4,62,630,10,0,15,9,'E');
INSERT INTO `listfiles` VALUES (30,367,51895,159,1868,1147,57,8,54,490,9,0,7,14,'E');
INSERT INTO `listfiles` VALUES (31,298,59302,498,3424,251,44,26,23,79,3,0,1,16,'E');
INSERT INTO `listfiles` VALUES (32,949,62657,511,4172,754,88,67,92,648,7,0,1,14,'E');
INSERT INTO `listfiles` VALUES (33,168,61496,545,3010,203,39,37,16,28,2,0,0,9,'E');
INSERT INTO `listfiles` VALUES (34,192,65178,526,3419,196,75,83,38,116,3,0,0,14,'E');
INSERT INTO `listfiles` VALUES (35,195,66234,469,2750,76,107,156,15,1,0,1,0,10,'E');
INSERT INTO `listfiles` VALUES (36,892,65824,767,4051,225,2,4,4,0,0,0,1,11,'E');
INSERT INTO `listfiles` VALUES (37,302,65117,416,2622,224,33,14,17,37,2,0,1,16,'E');
INSERT INTO `listfiles` VALUES (38,5155,67798,718,4460,399,29,6,50,464,9,0,8,21,'E');
INSERT INTO `listfiles` VALUES (39,9217,77003,561,3299,57,9,3,7,3,0,0,2,9,'E');
INSERT INTO `listfiles` VALUES (40,11369,61575,366,2373,67,6,3,7,46,7,0,2,4,'E');
INSERT INTO `listfiles` VALUES (41,323,59140,340,1829,87,37,26,16,43,3,0,1,11,'E');
INSERT INTO `listfiles` VALUES (42,7944,64633,712,5302,22,1,7,3,1,0,0,0,9,'E');
INSERT INTO `listfiles` VALUES (43,14256,71237,400,3434,26,5,2,4,0,0,0,2,44,'E');
INSERT INTO `listfiles` VALUES (44,4322,60646,387,1896,61,10,13,3,0,0,0,0,5,'E');
INSERT INTO `listfiles` VALUES (45,8868,77831,578,2460,16,15,3,4,0,0,1,1,6,'E');
INSERT INTO `listfiles` VALUES (46,1856,66992,716,3368,17,21,2,5,0,0,1,2,8,'E');
INSERT INTO `listfiles` VALUES (47,5026,56920,488,4617,349,27,8,28,196,7,0,3,17,'E');
INSERT INTO `listfiles` VALUES (48,2334,56587,203,1171,146,22,17,26,141,5,0,2,5,'E');
INSERT INTO `listfiles` VALUES (49,1157,57012,566,2873,314,44,21,28,154,5,0,1,13,'E');
INSERT INTO `listfiles` VALUES (50,508,61658,578,2548,207,96,89,51,80,2,0,1,25,'E');
/*!40000 ALTER TABLE `listfiles` ENABLE KEYS */;
UNLOCK TABLES;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;