No, I'm looking for the average of all not NULL columns inside a certain
_record/row_. There are other columns in the each row.
Right now I have to do this: (these are survey questions, which don't
necessarily need to be answered)
SELECT
IF(q1a,@cnt:=1,@cnt:=0),
IF(q1b,@cnt:=@cnt+1,0),
IF(q1c,@cnt:=@cnt+1,0),
IF(q1d,@cnt:=@cnt+1,0),
IF(q1e,@cnt:=@cnt+1,0),
IF(q1f,@cnt:=@cnt+1,0),
IF(q1g,@cnt:=@cnt+1,0),
IF(q1h,@cnt:=@cnt+1,0),
IF(q1i,@cnt:=@cnt+1,0),
IF(q1j,@cnt:=@cnt+1,0),
IF(q7,@cnt:=@cnt+1,0),
IF(q8,@cnt:=@cnt+1,0),
IF(q9,@cnt:=@cnt+1,0),
ROUND((q1a+q1b+q1c+q1d+q1e+q1f+q1g+q1h+q1i+q1j+q7+q8+q9)/@cnt,2) AS
surveyavg,
This works but looks really bad and I was wondering if there is another way
to do this. If this data were in columns, I could just AVG(q1a) and GROUP
BY q1a, but this data is in each row.
Ideas?
graeme
----- Original Message -----
From: "Cal Evans" <[EMAIL PROTECTED]>
To: "Graeme B. Davis" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, May 22, 2001 1:04 PM
Subject: Re: Average of all NON-NULL columns in a ROW?
> Select avg(id1) from <tableName> where id1 is not null; ?
>
> ----- Original Message -----
> From: "Graeme B. Davis" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, May 22, 2001 11:29 AM
> Subject: Average of all NON-NULL columns in a ROW?
>
>
> > Is there a way to get the AVG of all specified NON-NULL columns in one
> row?
> > Right now I am doing this in a little script, but it would be nice if I
> > could do something like this:
> >
> > DATA
> > ---------
> > id1 0 4 4 NULL 3 NULL
> >
> > I want the average of 0,4,4,3 ie (0+4+4+3)/4
> >
> > is there a way to do this in a SELECT query?
> >
> > Thanks!
> >
> > Graeme
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
