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

MySQLpivot

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 –

Can such output be generated using a single query?

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

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.