1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
|
SELECT
COUNT(*) AS "TOTAL",
ROUND(AVG(IF(R1!="0",R1-1,NULL)),1) AS "R1",
SUM(IF(`R1`!="0",1,0)) AS "R1E[]",
STD(NULLIF(`R1` ,0)) AS "R1D1",
ROUND((SUM(IF(`R1` IN("10","11"), 1,0)) / SUM(IF(`R1` > 0, 1,0)) - SUM(IF(`R1` IN("1","2","3","4","5","6","7"), 1,0)) / SUM(IF(`R1` > 0, 1,0))) * 100,0) as "RES",
ROUND(AVG(IF(`R2`!="0",R2,NULL)),1) AS "R2",
SUM(IF(`R2`!="0",1,0)) AS "R2E[]",
STD(NULLIF(`R2` ,0)) AS "R2D1",
ROUND(AVG(IF(`R3`!="0",R3,NULL)),1) AS "R3",
SUM(IF(`R3`!="0",1,0)) AS "R3E[]"
FROM `mytable`
WHERE `validation` BETWEEN TIMESTAMP("2017-01-01 00:00:00") AND TIMESTAMP("2017-06-15 23:59:59")
AND ((`RDT` BETWEEN '2017/04/01 00:00:00' AND '2017/04/30 23:59:59') OR (`RDT` BETWEEN '2017/03/01 00:00:00' AND '2017/03/31 23:59:59'))
UNION
SELECT
COUNT(*) AS "TOTAL",
ROUND(AVG(IF(R1!="0",R1-1,NULL)),1) AS "R1",
SUM(IF(`R1`!="0",1,0)) AS "R1E[]",
STD(NULLIF(`R1` ,0)) AS "R1D1",
ROUND((SUM(IF(`R1` IN("10","11"), 1,0)) / SUM(IF(`R1` > 0, 1,0)) - SUM(IF(`R1` IN("1","2","3","4","5","6","7"), 1,0)) / SUM(IF(`R1` > 0, 1,0))) * 100,0) as "RES",
ROUND(AVG(IF(`R2`!="0",R2,NULL)),1) AS "R2",
SUM(IF(`R2`!="0",1,0)) AS "R2E[]",
STD(NULLIF(`R2` ,0)) AS "R2D1",
ROUND(AVG(IF(`R3`!="0",R3,NULL)),1) AS "R3",
SUM(IF(`R3`!="0",1,0)) AS "R3E[]"
FROM `mytable`
WHERE `validation` BETWEEN TIMESTAMP("2017-01-01 00:00:00") AND TIMESTAMP("2017-06-15 23:59:59")
AND ((`RDT` BETWEEN '2017/06/01 00:00:00' AND '2017/06/30 23:59:59') OR (`RDT` BETWEEN '2017/05/01 00:00:00' AND '2017/05/31 23:59:59')) |
Partager