SELECT , GROUP AND DISPLAY IN TABULAR FORM AND COUNTING INSTANCES

by Rajorshe Mistry   Last Updated January 11, 2019 12:06 PM - source

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?

Tags : mysql


Answers 1


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
Akina
Akina
January 11, 2019 11:53 AM

Related Questions


The equivalent of CTE in MySQL from Microsoft SQL

Updated November 15, 2016 08:02 AM


getting innodb buffer usage

Updated August 24, 2018 15:06 PM