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;