469,890 Members | 2,164 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

Problematic IF statements

In 4.1.1-alpha-log the following happens.

Assume the following table and data:

CREATE TABLE exams (
pkey int(11) NOT NULL auto_increment,
name varchar(15),
exam int,
score int,
PRIMARY KEY (pkey)
);

insert into exams (name,exam,score) values ('Bob',1,75);
insert into exams (name,exam,score) values ('Bob',2,77);
insert into exams (name,exam,score) values ('Bob',3,78);
insert into exams (name,exam,score) values ('Bob',4,80);

insert into exams (name,exam,score) values ('Sue',1,90);
insert into exams (name,exam,score) values ('Sue',2,97);
insert into exams (name,exam,score) values ('Sue',3,98);
insert into exams (name,exam,score) values ('Sue',4,99);
mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob | 75 | 77 | 78 | 80 |
| Sue | 90 | 97 | 98 | 99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

The above is correct... it's a pivot query.

However IF statements fail after the first.

See here:

mysql> select name,
if(exam=1,score,null) as exam1,
if(exam=2,score,null) as exam2,
if(exam=3,score,null) as exam3,
if(exam=4,score,null) as exam4
from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob | 75 | NULL | NULL | NULL |
| Sue | 90 | NULL | NULL | NULL |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)
It there a way around this?
Regards,

Mike Chirico
Reference:
http://prdownloads.sourceforge.net/s...l.txt?download


Jul 20 '05 #1
0 943

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Andante.in.Blue | last post: by
39 posts views Thread by slogging_away | last post: by
reply views Thread by Fuzzyman | last post: by
20 posts views Thread by Neroku | last post: by
2 posts views Thread by ojorus | last post: by
reply views Thread by Gary Herron | last post: by
reply views Thread by Ohad Frand | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.