michal michal - 3 months ago 16
MySQL Question

MySQL SELECT with subqueries in PDO for monthly stats

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

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',
...
Comments