Mysql – SELECT , GROUP AND DISPLAY IN TABULAR FORM AND COUNTING INSTANCES

MySQLpivot

enter image description here

Answer to this query was received as –

SELECT location,
   AVG(CASE WHEN qtr = 'Q1' THEN achievement END) q1-average,
   AVG(CASE WHEN qtr = 'Q2' THEN achievement END) q2-average
FROM sourcetable 
GROUP BY location

Now, the problem is while counting the instances generated with the above query. The final output I require as shown below –

Final Output format

Can such output be generated using a single query?

Best Answer

SELECT location,
   AVG(CASE WHEN qtr = 'Q1' THEN achievement END) `q1-average`,
   AVG(CASE WHEN qtr = 'Q2' THEN achievement END) `q2-average`,
   COUNT(CASE WHEN achievement BETWEEN 3 AND 4 THEN 1 END) `3.0-4.0`,
   COUNT(CASE WHEN achievement BETWEEN 4 AND 5 THEN 1 END) `4.0-5.0`,
   COUNT(CASE WHEN achievement BETWEEN 5 AND 6 THEN 1 END) `5.0-6.0`,
   COUNT(CASE WHEN achievement BETWEEN 6 AND 7 THEN 1 END) `6.0-7.0`
FROM sourcetable 
GROUP BY location

In that form the border value (for example, 4.0) will be calculated both in lower and in upper range. To prevent this use double compare (for example COUNT(CASE WHEN achievement >= 3 AND achievement < 4 THEN 1 END)) instead of BETWEEN.