MySQL SELECT与PDO中的子查询进行月度统计

Question

I have a SELECT query that count how many defects found per month, and then I have another SELECT query that count how many defects fixed per month.

I am struggling to put them two together and calculate completion of FIXED/FOUND in each individual month.

FIXED:

 SELECT 
 MAX(myCount.7)'Jul',
 MAX(myCount.8)'Aug',
 MAX(myCount.9)'Sep',
 MAX(myCount.10)'Oct',
 MAX(myCount.11)'Nov',
 MAX(myCount.12)'Dec',
 MAX(myCount.1)'Jan',
 MAX(myCount.2)'Feb',
 MAX(myCount.3)'Mar',
 MAX(myCount.4)'Apr',
 MAX(myCount.5)'May',
 MAX(myCount.6)'Jun'
 FROM (SELECT 
  COUNT(IF(month(dDateClosed) = 7,1, NULL)) '7',
  COUNT(IF(month(dDateClosed) = 8,1, NULL)) '8',
  COUNT(IF(month(dDateClosed) = 9,1, NULL)) '9',
  COUNT(IF(month(dDateClosed) = 10,1, NULL)) '10',
  COUNT(IF(month(dDateClosed) = 11,1, NULL)) '11',
  COUNT(IF(month(dDateClosed) = 12,1, NULL)) '12',
  COUNT(IF(month(dDateClosed) = 1,1, NULL)) '1',
  COUNT(IF(month(dDateClosed) = 2,1, NULL)) '2',
  COUNT(IF(month(dDateClosed) = 3,1, NULL)) '3',
  COUNT(IF(month(dDateClosed) = 4,1, NULL)) '4',
  COUNT(IF(month(dDateClosed) = 5,1, NULL)) '5',
  COUNT(IF(month(dDateClosed) = 6,1, NULL)) '6'
 FROM T_Defects INNER JOIN T_settings
 WHERE (dDateClosed >= `FYDateFROM` AND dDateClosed <= `FYDateTO`)
 GROUP BY month(dDateClosed))myCount;

FOUND:

SELECT 
MAX(myCount.7)'Jul',
MAX(myCount.8)'Aug',
MAX(myCount.9)'Sep',
MAX(myCount.10)'Oct',
MAX(myCount.11)'Nov',
MAX(myCount.12)'Dec',
MAX(myCount.1)'Jan',
MAX(myCount.2)'Feb',
MAX(myCount.3)'Mar',
MAX(myCount.4)'Apr',
MAX(myCount.5)'May',
MAX(myCount.6)'Jun'
FROM (SELECT 
  COUNT(IF(month(dDateFound) = 7,1, NULL)) '7',
  COUNT(IF(month(dDateFound) = 8,1, NULL)) '8',
  COUNT(IF(month(dDateFound) = 9,1, NULL)) '9',
  COUNT(IF(month(dDateFound) = 10,1, NULL)) '10',
  COUNT(IF(month(dDateFound) = 11,1, NULL)) '11',
  COUNT(IF(month(dDateFound) = 12,1, NULL)) '12',
  COUNT(IF(month(dDateFound) = 1,1, NULL)) '1',
  COUNT(IF(month(dDateFound) = 2,1, NULL)) '2',
  COUNT(IF(month(dDateFound) = 3,1, NULL)) '3',
  COUNT(IF(month(dDateFound) = 4,1, NULL)) '4',
  COUNT(IF(month(dDateFound) = 5,1, NULL)) '5',
  COUNT(IF(month(dDateFound) = 6,1, NULL)) '6'
FROM T_Defects INNER JOIN T_settings
WHERE (dDateFound >= `FYDateFROM` AND dDateFound <= `FYDateTO`)
GROUP BY month(dDateFound))myCount;

Answer 1

Put them all in a big table, with columns for FOUND, columns for FIXED, and columns for the ratio fixed/found for each month

SELECT 
 MAX(myCount.7_found)'Jul_found',
 MAX(myCount.8_found)'Aug_found',
 ...
 MAX(myCount.7_fixed)'Jul_fixed',
 MAX(myCount.8_fixed)'Aug_fixed',
...
 MAX(myCount.7_fixed)/MAX(myCount.7_found) 'Jul_completion'
 MAX(myCount.8_fixed)/MAX(myCount.8_found) 'Aug_completion'
...

FROM (SELECT 
  COUNT(IF(month(dDateFound) = 7,1, NULL)) '7_found',
  COUNT(IF(month(dDateFound) = 8,1, NULL)) '8_found',
 ...
  COUNT(IF(month(dDateClosed) = 7,1, NULL)) '7_fixed',
  COUNT(IF(month(dDateClosed) = 8,1, NULL)) '8_fixed',
...