This is a continuation of a nasty problem I have to write a query for. The count was worked out but now need the average.
In a one table I have various attributes. The 3 in question are [admission_id] , [answer], and [mark]. The [admission_id] can have the same value in different rows. (e.g the value 235 can appear 3 times).
The [answer] can have either value 'Yes" or "No'. The [mark] will have a number value.
My query needs to find the average total of the [mark] for every distinct [admission_id] from the rows which has a 'Yes' value in the [answer] .
I have to list all the Admission_id entrys even those that have "no" in the [answer] column so there will be some so a simple SELECT admission_id, average /FROM table /WHERE [answer] = "Yes' doesn't appear to work as doesn't return all admission_id entrys
{Hopefully i explained it ok}
Thanks
In a one table I have various attributes. The 3 in question are [admission_id] , [answer], and [mark]. The [admission_id] can have the same value in different rows. (e.g the value 235 can appear 3 times).
The [answer] can have either value 'Yes" or "No'. The [mark] will have a number value.
My query needs to find the average total of the [mark] for every distinct [admission_id] from the rows which has a 'Yes' value in the [answer] .
I have to list all the Admission_id entrys even those that have "no" in the [answer] column so there will be some so a simple SELECT admission_id, average /FROM table /WHERE [answer] = "Yes' doesn't appear to work as doesn't return all admission_id entrys
{Hopefully i explained it ok}
Thanks
Last edited: