Thanks Roger, but it does not work:
SELECT CASE
when Species_1_e > 0
THEN 'extern'
when Species_3_e > 0
THEN 'intern'
ELSE ''
END AS `species_1_l` from species where statsample=1820;
----------Gives at a result 'extern'
What is the value of the Species_1_e column? Is it > 0? If so, 'extern' is the correct output. The CASE statement returns the value of the _first_ expression that is true, not the value from _all_ true expressions, as you seem to expect?
SELECT CASE
when Species_1_e > 0
THEN 'extern'
ELSE ''
END AS `species_1_l` from species where statsample=1820;
----------Gives at a result 'extern'
Same as above.
__and finally,
SELECT CASE
when Species_3_e > 0
THEN 'intern'
ELSE ''
END AS `species_1_l` from species where statsample=1820;
----------Gives at a result 'intern'
Here you are not testing the same column. If the value of Species_3_e is larger than 0, the result seems to be correct. Regardless of the value of Species_1_e.
I do not understand, why I can't get extern, intern as a result from this selection.
To get two rows in your result you must normally "match" two rows in your data. How many rows do you have where statsample=1820? One or two? If there are two, what are the values of Species_1_e and Species_3_e in these two rows?
The background is that I have a table with 195 rows and 1000 colums,
Ouch! You should read about normalization. Depending on what you are planning to do with your data, of course, but in general 1000 columns tells me that there is something seriously wrong with the table design. Your column names gives a hint, too...you should create at least one additional table, with one row for each repeated column in your current table, something like this:
create table species (
name char(4) not null primary key,
value int not null default 0);
insert into species values
('_1_e', 41241),
('_2_e', 0),
('_3_e', 123);Then you use JOINS to query your data.
<URL: http://dev.mysql.com/doc/mysql/en/join.html >
including counting data from species distributions of single samples. 999 species are the inventar of the whole area, but not all are present in each single sample (row). I want to select only columns from single rows, containing values > 0, or not NULL.
You can't select columns based on some condition, you can only select _rows_ based on conditions. Normalize!
<URL: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html >
-- Roger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
