CODEWITHSUNDEEP
phpmysql
Arif
Arif

Reputation: 1211

Compare mysql data within a table

I have a mysql table tbl_exam

user_ID | User_name | semester | math | english |
1       | John      | 1st      | 67   |  73     |
1       | John      | 2nd      | 82   |  59     |

after this, i want to compare 1st semester marks with 2nd semester.i.e Math is improving and English is degrading

user_ID | User_name | math  | english  |
1       | John      | good  | bad      |

I just fetch the result but have no idea how to solve.Any help would be appreciated

Upvotes: 3

Views: 169

Answers (3)

dnagirl
dnagirl

Reputation: 20446

You have the semester column, but semesters repeat each year. So if you were tracking John's complete academic career, you wouldn't be able to tell which year a particular 2nd semester belonged to.

Secondly, your schema is not normalized. There should be one table students with user_id and user_name. There should be a courses table with course_id, course_name, course_date. And there should be a marks table with mark_id, course_id, semester enum('1st','2nd') not null, user_id, mark. The marks table could have a unique id composed of course_id and semester and user_id, which will prevent a student from having more than one mark per semester per course and will logically mean that each year the courses table will get a new record for each course offered even if that course is the same as last year's course.

Given the above, the query would be something like:

SELECT m1.user_id, m1.course_id, 
  CASE WHEN m1.mark > m2.mark THEN 'bad' 
       WHEN m1.mark < m2.mark THEN 'good' 
       ELSE 'same' END AS trend
FROM marks m1 JOIN marks m2 
  ON (m1.user_id = m2.user_id AND m1.course_id= m2.course_id)
WHERE m1.semester=1 AND m2.semester=2;

which would give this:

user_id, course_id, trend
      1,         1, 'good'
      1,         2, 'bad'

To substitute English for the ids,

SELECT user_name, course_name, 
      CASE WHEN m1.mark > m2.mark THEN 'bad' 
           WHEN m1.mark < m2.mark THEN 'good' 
           ELSE 'same' END AS trend
FROM marks m1 JOIN marks m2 
  ON (m1.user_id = m2.user_id AND m1.course_id= m2.course_id)
  JOIN users u ON (m1.user_id=u.user_id)
  JOIN courses c ON (m1.course_id=c.course_id)
WHERE m1.semester=1 AND m2.semester=2;

which would give this:

user_id, course_id, trend
   John,      math, 'good'
   John,   english, 'bad'

You could fiddle around with aggregate functions and pivot the output. But since you're outputting with PHP, I'd probably do the pivot there. That allows you to handle an arbitrary number of courses without knowing what they will be ahead of time.

Upvotes: 1

echo_Me
echo_Me

Reputation: 37243

your table seems little wrong , because John have 2 user_ID . it should be like that

  ID |user_ID | User_name | semester | math | english |
   1 |1       | John      | 1st      | 67   |  73     |
   2 |1       | John      | 2nd      | 82   |  59     |

like that user_ID is same with John only ID will be incremented.

Upvotes: 2

Saharsh Shah
Saharsh Shah

Reputation: 29071

Try this: 

SELECT A.user_id, A.user_name, IF((B.math - A.math) > 0, 'good', 'bad') math, 
         IF((B.english - A.english) > 0, 'good', 'bad') english
FROM (SELECT user_id, user_name, math, english 
FROM tbl_exam WHERE semester = '1st') AS A 
INNER JOIN 
(SELECT user_id, user_name, math, english 
FROM tbl_exam WHERE semester = '2nd') AS B ON A.user_id = B.user_id

Upvotes: 2

Related Questions