Average except for 0

Average except for 0

Post by Kalen Delane » Sun, 07 May 2000 04:00:00



If you were using Microsoft SQL Server, which is the product this newsgroup
is related to, you could use the NULLIF function , to treat your 0's as
NULLs for the averaging operating.

You would have better luck posting this on an Oracle oriented newsgroup.

--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
     www.TheHungerSite.com


Quote:> Hi,

> I have a database that records the score that a student has received for a
> test.
> A value of 0 has been used to signify that that student did not do that
> test.

> What I am trying to do is get averages of both the overall and the
> individual.
> So if a zero has been entered, do not use it for the averaging but I still
> need to keep it in the database for reporting purposes.

> I am using Oracle 8.0.5 and VB to access the database.

> Student_Number    Question    Score    date
>       1                            1                0    1/1/00
>       1                            2                5    1/1/00
>       2                            1                3    1/1/00
>       2                            2                5    1/1/00
>       1                            1                3    2/1/00
>       1                            2                4    2/1/00
>       2                            1                4    2/1/00
>       2                            2                4    2/1/00

> What I want is to be able to get the following:

>                         Date        Question     Rating
> Overall               1/1/00         1              3
>                                              2              5
> Individual 1         1/1/00        1              0
>                                              2              5
> etc.

> Overall                All Dates    1              3.33
>                                              2              4.5
> Indivual 1            All Dates    1               3
>                                              2               4.5

> Any help VERY MUCH appreciated.

> Steve

 
 
 

Average except for 0

Post by BPMargoli » Sun, 07 May 2000 04:00:00


Steven,

If you are using Oracle 8.0.5, why are you posting to a Microsoft SQL Server
newsgroup?

Nevertheless, the following should work on SQL Server. I have no idea whether it
will work in Oracle.

Use the ANSI SQL-92 standard CASE statement to test for zeroes, and change them
to NULLs. NULLs are automatically excluded from the AVG function.

create table Steve
(
 Student_Number int,
 Question int,
 Score int,
 TestDate datetime
)
go

insert into Steve values (1, 1, 0, 'Jan 1 2000')
insert into Steve values (1, 2, 5, 'Jan 1 2000')
insert into Steve values (2, 1, 3, 'Jan 1 2000')
insert into Steve values (2, 2, 5, 'Jan 1 2000')
insert into Steve values (1, 1, 3, 'Jan 1 2000')
insert into Steve values (1, 2, 4, 'Jan 1 2000')
insert into Steve values (2, 1, 4, 'Jan 1 2000')
insert into Steve values (2, 2, 4, 'Jan 1 2000')

select Student_Number, TestDate, Question,
          Score = avg(case when Score = 0 then NULL else Score end)
from Steve
group by Student_Number, TestDate, Question
order by Student_Number, TestDate, Question

---------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be
cut and pasted into Query Analyzer is appreciated.


Quote:> Hi,

> I have a database that records the score that a student has received for a
> test.
> A value of 0 has been used to signify that that student did not do that
> test.

> What I am trying to do is get averages of both the overall and the
> individual.
> So if a zero has been entered, do not use it for the averaging but I still
> need to keep it in the database for reporting purposes.

> I am using Oracle 8.0.5 and VB to access the database.

> Student_Number    Question    Score    date
>       1                            1                0    1/1/00
>       1                            2                5    1/1/00
>       2                            1                3    1/1/00
>       2                            2                5    1/1/00
>       1                            1                3    2/1/00
>       1                            2                4    2/1/00
>       2                            1                4    2/1/00
>       2                            2                4    2/1/00

> What I want is to be able to get the following:

>                         Date        Question     Rating
> Overall               1/1/00         1              3
>                                              2              5
> Individual 1         1/1/00        1              0
>                                              2              5
> etc.

> Overall                All Dates    1              3.33
>                                              2              4.5
> Indivual 1            All Dates    1               3
>                                              2               4.5

> Any help VERY MUCH appreciated.

> Steve


 
 
 

Average except for 0

Post by Steven Rei » Mon, 08 May 2000 04:00:00


Hi,

I have a database that records the score that a student has received for a
test.
A value of 0 has been used to signify that that student did not do that
test.

What I am trying to do is get averages of both the overall and the
individual.
So if a zero has been entered, do not use it for the averaging but I still
need to keep it in the database for reporting purposes.

I am using Oracle 8.0.5 and VB to access the database.

Student_Number    Question    Score    date
      1                            1                0    1/1/00
      1                            2                5    1/1/00
      2                            1                3    1/1/00
      2                            2                5    1/1/00
      1                            1                3    2/1/00
      1                            2                4    2/1/00
      2                            1                4    2/1/00
      2                            2                4    2/1/00

What I want is to be able to get the following:

                        Date        Question     Rating
Overall               1/1/00         1              3
                                             2              5
Individual 1         1/1/00        1              0
                                             2              5
etc.

Overall                All Dates    1              3.33
                                             2              4.5
Indivual 1            All Dates    1               3
                                             2               4.5

Any help VERY MUCH appreciated.

Steve

 
 
 

Average except for 0

Post by Kim Berghal » Mon, 08 May 2000 04:00:00


I would just add a simple where clause (use union all to combine multiple
select statements).

select 'Overall' as name, testdate, question, avg(score)
from yourtable
where score > 0
group by testdate, question
union all
select studentname, testdate, question, score
from yourtable
group by studentname, testdate, question
union all
select 'Overall' as name, testdates, 'all questions', avg(score)
from yourtable
where score > 0
group by testdate
union all
select studentname, testdate, question, score
from yourtable
group by studentname, testdate

This is only a "concept" SQL, you need to verify the syntax etc. to fit your
table layout.

--
Kim Berghall
Sisu Group, Inc.
remove no_spam.

www.sisugrp.com


Quote:> Hi,

> I have a database that records the score that a student has received for a
> test.
> A value of 0 has been used to signify that that student did not do that
> test.

> What I am trying to do is get averages of both the overall and the
> individual.
> So if a zero has been entered, do not use it for the averaging but I still
> need to keep it in the database for reporting purposes.

> I am using Oracle 8.0.5 and VB to access the database.

> Student_Number    Question    Score    date
>       1                            1                0    1/1/00
>       1                            2                5    1/1/00
>       2                            1                3    1/1/00
>       2                            2                5    1/1/00
>       1                            1                3    2/1/00
>       1                            2                4    2/1/00
>       2                            1                4    2/1/00
>       2                            2                4    2/1/00

> What I want is to be able to get the following:

>                         Date        Question     Rating
> Overall               1/1/00         1              3
>                                              2              5
> Individual 1         1/1/00        1              0
>                                              2              5
> etc.

> Overall                All Dates    1              3.33
>                                              2              4.5
> Indivual 1            All Dates    1               3
>                                              2               4.5

> Any help VERY MUCH appreciated.

> Steve

 
 
 

Average except for 0

Post by Umachandar Jayachandra » Mon, 08 May 2000 04:00:00


Try:

SELECT student_number, AVG( DECODE( Score, 0, NULL ) ) AS Rating
FROM tbl
GROUP BY student_number

-- DECODE is similar to CASE

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm

 
 
 

Average except for 0

Post by Steven Rei » Tue, 09 May 2000 04:00:00


Thank you, BPMargolin  and Karen for your replies.

I  thought that it would be a good idea to check with the SQL Server users,
to see if there was a 'generic' sql way of handling this.

Sorry if I ruffled any feathers.  :-)

Steve

> Steven,

> If you are using Oracle 8.0.5, why are you posting to a Microsoft SQL
Server
> newsgroup?

> Nevertheless, the following should work on SQL Server. I have no idea
whether it
> will work in Oracle.

> Use the ANSI SQL-92 standard CASE statement to test for zeroes, and change
them
> to NULLs. NULLs are automatically excluded from the AVG function.

> create table Steve
> (
>  Student_Number int,
>  Question int,
>  Score int,
>  TestDate datetime
> )
> go

> insert into Steve values (1, 1, 0, 'Jan 1 2000')
> insert into Steve values (1, 2, 5, 'Jan 1 2000')
> insert into Steve values (2, 1, 3, 'Jan 1 2000')
> insert into Steve values (2, 2, 5, 'Jan 1 2000')
> insert into Steve values (1, 1, 3, 'Jan 1 2000')
> insert into Steve values (1, 2, 4, 'Jan 1 2000')
> insert into Steve values (2, 1, 4, 'Jan 1 2000')
> insert into Steve values (2, 2, 4, 'Jan 1 2000')

> select Student_Number, TestDate, Question,
>           Score = avg(case when Score = 0 then NULL else Score end)
> from Steve
> group by Student_Number, TestDate, Question
> order by Student_Number, TestDate, Question

> ---------------------------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which
can be
> cut and pasted into Query Analyzer is appreciated.



> > Hi,

> > I have a database that records the score that a student has received for
a
> > test.
> > A value of 0 has been used to signify that that student did not do that
> > test.

> > What I am trying to do is get averages of both the overall and the
> > individual.
> > So if a zero has been entered, do not use it for the averaging but I
still
> > need to keep it in the database for reporting purposes.

> > I am using Oracle 8.0.5 and VB to access the database.

> > Student_Number    Question    Score    date
> >       1                            1                0    1/1/00
> >       1                            2                5    1/1/00
> >       2                            1                3    1/1/00
> >       2                            2                5    1/1/00
> >       1                            1                3    2/1/00
> >       1                            2                4    2/1/00
> >       2                            1                4    2/1/00
> >       2                            2                4    2/1/00

> > What I want is to be able to get the following:

> >                         Date        Question     Rating
> > Overall               1/1/00         1              3
> >                                              2              5
> > Individual 1         1/1/00        1              0
> >                                              2              5
> > etc.

> > Overall                All Dates    1              3.33
> >                                              2              4.5
> > Indivual 1            All Dates    1               3
> >                                              2               4.5

> > Any help VERY MUCH appreciated.

> > Steve