0

DATA TABLE

I Have a table as above. How can I get an output (as shown below) with a single mysql query.

enter image description here

1
SELECT location,
       AVG(CASE WHEN qtr = 'Q1' THEN achievement END) q1-average,
       AVG(CASE WHEN qtr = 'Q2' THEN achievement END) q2-average
FROM sourcetable
-- WHERE year = '2018'
GROUP BY location;
  • Thanks so very much :-). It really works and solved my problem. – Rajorshe Mistry Jan 10 at 5:42
  • Another issue. From the output generated with the query above, now I want to count for Location-1 and Q1-AVERAGE / Q2-AVERAGE, how many falls in the range between 3.0-4.0, 4.0-5.0, 5.0-6.0, 6.0-7.0 etc. Similarly for Location-2 and Location-3. Can it be done in a single query on top of the above query? – Rajorshe Mistry Jan 11 at 10:08
  • @RajorsheMistry how many falls in the range between 3.0-4.0 What's a problem? COUNT(CASE WHEN location = 'location 1' AND achievement BETWEEN 3.0 AND 4.0 THEN 1 END). Remember - if condition not matched (none condition matched if there is more than one WHEN), CASE returns NULL, which is ignored by COUNT/SUM/AVG. – Akina Jan 11 at 10:26
  • Yes. That's the query I was looking for. But I want to use it in the same query. Can it be used like this - COUNT (CASE WHEN LOCATION='Location-1' and achievement BETWEEN 3.0 AND 4.0 THEN1 END (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) – Rajorshe Mistry Jan 11 at 10:42
  • @RajorsheMistry Can it be used Of course... the grouping frame is the same, so simply add calculated fields to SELECT clause. – Akina Jan 11 at 11:39
-1
SELECT location,
       AVG(SELECT achievment FROM location WHERE QTR='Q1'),
       AVG(SELECT achievment FROM location WHERE QTR='Q2') 
FROM location 
GROUP BY location
  • Wrong query. Test it on shown data... – Akina Jan 10 at 5:37
  • This query is not working on the test as well as on actual data. – Rajorshe Mistry Jan 11 at 10:15

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.