CODEWITHSUNDEEP
c#sqldatabasedatagridviewaverage
Niek Jonkman
Niek Jonkman

Reputation: 497

Average numbers in my SQL query

I have a question regarding my SQL query. Below you will see my database:

My database

And I have the following query right now:

SELECT enquete_vraag,enquete_antwoord,docent,vak,semesterstart
FROM ENQUETE_ANTWOORD
LEFT JOIN KDV ON ENQUETE_ANTWOORD.kdv_ID = KDV.kdv_ID
LEFT JOIN DOCENT ON KDV.docent_ID = DOCENT.docent_ID
LEFT JOIN VAK ON KDV.vak_ID = VAK.vak_ID
LEFT JOIN ENQUETE_VRAAG ON ENQUETE_ANTWOORD.enquete_vraag_ID = ENQUETE_VRAAG.enquete_vraag_ID
WHERE DOCENT.docent_ID = variableDocentID AND VAK.vak = variableVak

And I display the returned data in a datagridview:

Datagridview

Now the datagridview shows all questions that are being answered by all students. What I want is the average for each question and only show that. So you have 1 row that has question 6 with the average answer and question 7 with the average answer and so on, how do I achieve that in my SQL query?

Upvotes: 0

Views: 134

Answers (3)

abiansh
abiansh

Reputation: 79

just add avg function in your code.

...AVG(enquete_antwoord)...

write above code it gives you the correct answer

Upvotes: 0

Raphael
Raphael

Reputation: 1687

hi you should do something like this

SELECT enquete_vraag,AVG(enquete_antwoord) enquete_antwoord,docent,vak,semesterstart 
FROM ENQUETE_ANTWOORD 
LEFT JOIN KDV ON ENQUETE_ANTWOORD.kdv_ID = KDV.kdv_ID 
LEFT JOIN DOCENT ON KDV.docent_ID = DOCENT.docent_ID 
LEFT JOIN VAK ON KDV.vak_ID = VAK.vak_ID 
LEFT JOIN ENQUETE_VRAAG ON ENQUETE_ANTWOORD.enquete_vraag_ID = ENQUETE_VRAAG.enquete_vraag_ID 
WHERE DOCENT.docent_ID = variableDocentID AND VAK.vak = variableVak 
GROUP BY enquete_vraag, docent,vak,semesterstart

Upvotes: 2

Marc Gravell
Marc Gravell

Reputation: 1063774

SELECT enquete_vraag,AVG(enquete_antwoord) as [enquete_antwoord]
FROM ...
GROUP BY enquete_vraag

The problem then, of course, becomes which vak etc to choose.... Because of translation, it is not easy for me to guess at which value means what, so it is hard to advise on that. You might be able to include the extra values in the GROUP BY clause (if they are the same for all the matching rows); or you might be able to take a MIN / MAX.

Upvotes: 1

Related Questions