SQL query

Question

Found randomly somewhere:

i think multiple answers possible? 

Comments

In this case COUNT of single attribute might be different from COUNT of whole table.

---

result using SUM and COUNT is always equal to result using AVG if there is atleast one non-NULL value, because all three of SUM, COUNT and AVG ignore NULL values.

but there is problem in one case, if all the values are NULL under rating attribute, in this case COUNT=0, SUM=NULL, AVG=NULL

---

yes i think some issue is there.,

---

If out of 3 values in rating attr, 1 is null , then COUNT(rating) = 2, but COUNT(whole relation) = 3.

Is this Q only related to that "rating" attribute?

---

still if we find answer b) will be ans

---

Average in SQL is sum of all non-null values/total non-null values?

or sum of all non-null values/total values(null+non null) ?

---

//Assuming you are aware of the sum() and count().

 

     Avg = sum of non-null values/count of non-null values

i.e. Avg <=> sum(column_name)/count(column_name)

Eg: .........| Salary| ..........

               |   500 |

               |   Null |

               |   300  |

              

          Avg(Salary) = 400

<=> sum(Salary) / count(Salary)  = 800 / 2 = 400

Answer 1

The answer could be B or C.

the twist is in the COUNT function, if count function is applied on whole table, COUNT(*), then the answer will be C.

and if the COUNT function is applied on rating attribute, COUNT(rating), then the result will be equal to that of AVERAGE function B.