Popular Posts

There was an error in this gadget

Aug 13, 2011

Using CASE in mysql


Some days ago i have face a little problem in sql query.
The problem was say i have a table like this

Create the table and dumping the data

Create table student_marks(
id int(10) not null auto_increment,
subject_name varchar(10),
student_name varchar(10),
marks varchar(12),
primary key(id)
);

INSERT INTO student_marks(subject_name, student_name, marks)
Values('Bangla', 'sagar', '75');

INSERT INTO student_marks(subject_name, student_name, marks)
Values('Bangla', 'jubayer', '80');

INSERT INTO student_marks(subject_name, student_name, marks)
Values('Bangla', 'sujon', '72');

INSERT INTO student_marks(subject_name, student_name, marks)
Values('English', 'sagar', '70');

INSERT INTO student_marks(subject_name, student_name, marks)
Values('English', 'jubayer', '85');


mysql> select * from student_marks;
+----+--------------+--------------+-------+
| id | subject_name | student_name | marks |
+----+--------------+--------------+-------+
|  1 | Bangla       | sagar        | 75    |
|  2 | Bangla       | jubayer      | 80    |
|  3 | Bangla       | sujon        | 72    |
|  4 | English      | sagar        | 70    |
|  5 | English      | jubayer      | 85    |
+----+--------------+--------------+-------+
5 rows in set (0.03 sec)

And the output will be

+--------------+--------+---------+
| student_name | Bangla | English |
+--------------+--------+---------+
| jubayer      |     80 |      85 |
| sagar        |     75 |      70 |
| sujon        |     72 |       0 |
+--------------+--------+---------+
3 rows in set (0.06 sec)

To solve this problem we have to use if/else or CASE in the query.

So the query will be like this:

mysql>  SELECT student_name,
    ->      SUM(CASE when subject_name LIKE 'bangla' THEN marks else 0 end) as B
angla,
    ->      SUM(CASE when subject_name LIKE 'english' THEN marks else 0 end) as
English
    ->      FROM student_marks GROUP BY student_name;

This query give me the out put what i expected....
Now i am facing a new problem... if the subject_name is number of n then what will be th solution?

Find how much old you r:
select *, case when ((datediff(DATE(NOW()), dob)/365)>18) then 1 else 0 end as adult from users;

No comments:

Post a Comment