CODEWITHSUNDEEP
mysqlsqlpivot-tablecrosstab
Rob Sands
Rob Sands

Reputation: 65

Cross Tab Query in MySQL

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!

Upvotes: 0

Views: 843

Answers (1)

1000111
1000111

Reputation: 13519

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

Upvotes: 1

Related Questions