SQL Select ISNULL not working

Question

enter image description here

I used the query below to extract the data fro the table but it does not working.

     SELECT A.ID, AVG(ISNULL(score,0)) AS sc FROM A 
               LEFT OUTER JOIN B ON A.ID = B.ID 
            WHERE A.aClass = '1st'

I wanted it to return all the data from Table A with its corresponding average score and return 0 if there is no score yet. Can anyone help me figure out the problem.

Why do you have a duplicated ID in table b?

mitchimus
– mitchimus

2014-02-05 00:51:41 +00:00
Commented Feb 5, 2014 at 0:51 — mitchimus
– mitchimus, Commented Feb 5, 2014 at 0:51
That is for example ID 1 has two records of score.

eirishainjel
– eirishainjel

2014-02-05 00:53:13 +00:00
Commented Feb 5, 2014 at 0:53 — eirishainjel
– eirishainjel, Commented Feb 5, 2014 at 0:53

user2989408 · Accepted Answer · 2014-02-05 00:52:23Z

1

Try this

 SELECT A.ID, AVG(ISNULL(B.score,0)) AS sc 
 FROM A 
     LEFT OUTER JOIN B ON A.ID = B.ID 
 WHERE A.aClass = '1st'
 GROUP BY A.ID

answered Feb 5, 2014 at 0:52

user2989408

3,1371 gold badge19 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Select ISNULL not working

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related