How to Count Average from table databases

Question

i want to Count average from my databases.

for example when i run a sql query like this

SELECT user.nama_u AS Nama_Siswa,
       nilai.nilai_n AS Nilai_Pelajaran
       FROM nilai,
            user
       WHERE nilai.id_sw = user.id_u
             AND user.pangkat_u = 'Siswa'
             AND nilai.kelas_n = 'I'
       ORDER BY user.nama_u DESC

i have table like this

Nama_Siswa               Nilai_Pelajaran
===============          ===============     
Teuku Zul Hidayat        75
Teuku Zul Hidayat        60 
Ferry Mulia Ramadhani    100
Ferry Mulia Ramadhani    80
Apri Sara Diwa           80
Apri Sara Diwa           70

what i have modify from sql query from above if i want to show average of Nilai_Pelajaran?

so the table must be like this

Nama_Siswa               Average
===============          ===============     
Teuku Zul Hidayat        67.5
Ferry Mulia Ramadhani    90
Apri Sara Diwa           75

Note that we stopped writing queries this way circa 1992. Come. JOIN us. — Strawberry
– Strawberry, Commented Aug 25, 2018 at 8:00

Nick · Accepted Answer · 2018-08-25 04:44:05Z

2

Just change your query to use aggregate functions (in this case AVG) and GROUP BY nama_u:

SELECT user.nama_u AS Nama_Siswa, AVG(nilai.nilai_n) AS Nilai_Pelajaran 
FROM nilai
JOIN user 
WHERE nilai.id_sw = user.id_u AND user.pangkat_u = 'Siswa' AND nilai.kelas_n = 'I' 
GROUP BY user.nama_u
ORDER BY user.nama_u DESC

answered Aug 25, 2018 at 4:44

Nick

147k23 gold badges67 silver badges106 bronze badges

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

2 Comments

Strawberry Over a year ago

Note that we normally use the JOIN...ON construction

Nick Over a year ago

@Strawberry indeed. However I didn't want to change any more of OPs query than was absolutely necessary (i.e. changing , to JOIN :-).

Collectives™ on Stack Overflow

How to Count Average from table databases

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related