0

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?

0
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.

  • I tried with COUNT(CASE WHEN achievement >= 3 AND achievement < 4 THEN 1 END) and it perfectly works. Output is exactly what I was looking for. Thank your so much :-)) – Rajorshe Mistry Jan 14 at 5:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.