3

I am trying to create an AVG of student scores in classic asp. Here is my student table:

Name   Q1   Q2
--------------
John   92   84
John   89   0
John   0    0

Here is my sql query:

SELECT Name, SUM(Q1)/count(*) AS Q1, SUM(Q2)/count(*) AS Q2
FROM table Student

"0" should not be counted.

Q1 should be 

92+89/2

or Q2 should be 

84/1

However, the result for both always divide by 3
How do I subtract 1 from any row that has 0?

user692942
  • 16,398
  • 7
  • 76
  • 175
Jenny Tran
  • 251
  • 5
  • 16
  • 4
    AVG function https://msdn.microsoft.com/en-us/library/ms177677.aspx – sam yi Aug 13 '15 at 14:47
  • 4
    See [tsql-average of all values in column that are not zero](http://stackoverflow.com/questions/4751772/tsql-average-of-all-values-in-a-column-that-are-not-zero) and give credit where credit is due if answer helps! The trick here is knowing null values aren't part of the count used in the denominator for calculating average! – xQbert Aug 13 '15 at 14:54

2 Answers2

13

First of all, use AVG instead of SUM - it does all the work for you.

To ignore the zero scores in your situation (where you could have zero for Q1 and non-zero for Q2), use the NULLIF function to change the zeros to nulls. SQL will ignore the null values when calculating the average:

SELECT
  Name,
  AVG(NULLIF(Q1, 0)) AS Q1,
  AVG(NULLIF(Q2, 0)) AS Q2
FROM tableStudent;

The above query gives overall results for all students. If you want results by student you'll need to GROUP BY:

SELECT
  Name,
  AVG(NULLIF(Q1, 0)) AS Q1,
  AVG(NULLIF(Q2, 0)) AS Q2
FROM tableStudent
GROUP BY Name;
Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
1

You can use a CASE statement:

SELECT Name, SUM(CASE WHEN Q1 > 0 THEN Q1 ELSE 0 END) / COUNT(*) AS Q1,
    SUM(CASE WHEN Q2 > 0 THEN Q2 ELSE 0 END) / COUNT(*) AS Q2
FROM table Student
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
SWiggels
  • 2,159
  • 1
  • 21
  • 35