Cross Tab Query in MySQL

Question

I created a sql select query, but I cannot get the records to display correctly.

Table:

gradeid | usrname | reviewmonth | program | total_score | pae
--------------------------------------------------------------
151     | smithj  |      2      | math    | 100%        | 100%
152     | smithj  |      2      | math    |  95%        | 100%
153     | smithj  |      3      | math    |  80%        | 100%
154     | jonesm  |      3      | math    |  79%        | 79% 
155     | jonesj  |      2      | art     |  100%       | 100%

The query that I created to display the information is

SELECT reviewmonth, 
ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score)),2) AS January
FROM vwscore 
WHERE program = 'Math' AND reviewmonth = 1 
UNION ALL
SELECT reviewmonth, 
ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score)),2) AS February
FROM vwscore 
WHERE program = 'Math' AND reviewmonth = 2 
UNION ALL
SELECT reviewmonth, 
ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score)),2) AS March
FROM vwscore 
WHERE program = 'Math' AND reviewmonth =  3
UNION ALL

The query returns: Unfortunately I need to display the records horizontally.

reviewmonth | January 
----------------------
      1     |  91.94
      2     |  94.86
      3     |  89.89

Desired outcome:

January | February | March
--------------------------
   91.94|   94.86  |  89.89

I tried different queries to display the answer. I tried using CASE, but it seemed to only display 1 record. Thanks for all your help!

http://stackoverflow.com/questions/7674786/mysql-pivot-table — sqluser, Mar 04 '15 at 05:25
SELECT COUNT(CASE WHEN reviewmonth = 1 THEN ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae, total_score)), total_score)),2) ELSE NULL END) AS 'January', COUNT(CASE WHEN reviewmonth = 2 THEN ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae, total_score)), total_score)),2) ELSE NULL END) AS 'February', COUNT(CASE WHEN reviewmonth = 3 THEN ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae, total_score)), total_score)),2) ELSE NULL END) AS 'March' FROM vwscore WHERE program = 'math' — Rob Sands, Mar 04 '15 at 05:39
I received this error: [Err] 1111 - Invalid use of group function. Thanks for the help. Am I missing something. I follow the sql from the link provided. — Rob Sands, Mar 04 '15 at 05:40

1000111 · Accepted Answer · 2015-03-04T07:29:29.473

This query fulfills the horizontal demand of your question.

I am not concerned what you are trying to do.

Please make sure if this works for you.

SELECT 
CASE WHEN  program = 'Math' AND reviewmonth = 1 THEN
        ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score)),2) 
        ELSE 'NULL' END AS January,
CASE WHEN program= 'Math' AND reviewmonth = 2 THEN
        ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score)),2) 
    ELSE 'NULL' END AS February,

CASE WHEN program= 'Math' AND reviewmonth = 3 THEN
        ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score)),2)
    ELSE 'NULL' END AS March
FROM vwscore
WHERE program='Math'

EDIT : I was not concerned about what you expected.Now you will get the correct result I hope.

SELECT
ROUND(t.January/t.JanuaryCount,2) AS January,
ROUND(t.February/t.FebruaryCount,2) AS February,
ROUND(t.March/t.MarchCount,2) AS March
FROM 
(
    SELECT
            SUM(CASE WHEN reviewmonth=1 THEN IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score) ELSE 0 END) AS January,
            SUM(CASE WHEN reviewmonth=1 THEN 1 ELSE 0 END) AS JanuaryCount,

            SUM(CASE WHEN reviewmonth=2 THEN IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score) ELSE 0 END) AS February,
            SUM(CASE WHEN reviewmonth=2 THEN 1 ELSE 0 END) AS FebruaryCount,

            SUM(CASE WHEN reviewmonth=3 THEN IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score) ELSE 0 END) AS March,
            SUM(CASE WHEN reviewmonth=3 THEN 1 ELSE 0 END) AS MarchCount

    FROM 
    vwscore
    WHERE program='Math'
) AS t

Thanks for the help. The query works just like you stated. I think there is a problem with my table. The return record set returned: January | Null ; February | 92.19; March | Null. The interesting thing is that it groups everything in the month of February. Thanks again...I am puzzled. — Rob Sands, Mar 04 '15 at 06:16
You are welcome Rob!! Glad to hear that it worked for you. :) Have a nice day! — 1000111, Mar 04 '15 at 06:18
It looks like if all rolls up to February. I know the query makes sense. The rest are NULL — Rob Sands, Mar 04 '15 at 06:23
Hi Rob! Now check the edited query. Please make sure that you got it right! — 1000111, Mar 04 '15 at 07:50
The updated answer from Md. Afafat Al Mahmud worked! I'm very impressed on how you came up with the answer. Thanks you so much!!! Thank you all again for all the help!!!! — Rob Sands, Mar 04 '15 at 15:18

Cross Tab Query in MySQL

1 Answers1