Gobi <[EMAIL PROTECTED]> wrote on 11/28/2005 10:34:37 AM: > Felix Geerinckx wrote: > > >On 28/11/2005, Gobi wrote: > > > > > > > >>I need to write a select clause based on the following conditions: > >> > >>If all rows of status1 == "Neg" > >>count all "Neg" rows in status1 > >>else > >>check if all rows of status2 == "Neg" > >> count all "Neg" rows in status2 and status1 > >>endif > >>endif > >> > >> > > > >Not sure if I understand this completely. Does the following give what > >you want? > > > >SELECT > > CASE > > WHEN MAX(status1) = MIN(status1) AND status1 = 'Neg' THEN > > SUM(IF(status1 = 'Neg', 1, 0)) > > WHEN MAX(status2) = MIN(status2) AND status2 = 'Neg' THEN > > SUM(IF(status1 = 'Neg', 1, 0) + > > IF(status2 = 'Neg', 1, 0)) > > ELSE NULL > > END AS count > >FROM foo; > > > > > > > Not exactly but thanks for trying. I currently have a filter which > works on one column: > > select count(*) from dummy where status1 = 'Neg' > and not exists (select 1 from dummy where status1 = 'Pos'); > > The above query filters out ALL the rows if even one row containing > 'Pos' or 'Ind' exists. Now, what I want to do is to extend this query > so that it searches status1 first. If the above query on status1 fails > (that is one row of 'Pos' or 'Ind' exists), then I have to check > status2. If all the rows in status2 = "Neg" then I can count the "Neg" > rows in column1. On the other hand, if the condition succeeds then only > rows with status1 = "Neg" is counted. > > Hope that clarifies my problem. >
Wouldn't it be just as simple to count all 'neg', 'pos', and 'ind' values for both columns? By the way, is there some other column you are grouping by or is it always going to apply to entire tables of information? I know this is not what you wanted. However, what you want still isn't very clear to me but this might help you get better data for your application to make decisions from: SELECT sum(if(status1='neg',1,0)) as neg1 ,sum(if(status1='pos',1,0)) as pos1 ,sum(if(status2='neg',1,0)) as neg2 ,sum(if(status2='pos',1,0)) as pos2 ,sum(if(status2='neg' AND status1='neg',2,0)) as neg_both FROM dummy; If you had another column in your data like a patientID or a groupID it would be very simple to add that column and group by that value with this query. As I said, what you are trying to get at is still fuzzy for me (because you oversimplified the real issue). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
