The way I see it, you'll need to make another query with AVG() function after. So, let's take your current query and inspect where you need to fix:
SELECT setoran.id_user, user.id_user, user.nama, setoran.id_sikap,
AVG(kelancaran) AS avg_kelancaran, AVG(tahsin) AS avg_tahsin,
/*^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
the part above is fine */
(tahsin + kelancaran)/2 AS akhir,
/*^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
I'm a bit curious how you calculate this but I think you're looking for the
combined value of avg and divide by 2. I'll do this on the second part of the
query*/
ROW_NUMBER()
OVER (PARTITION BY setoran.id_user
ORDER BY COUNT(setoran.id_sikap) DESC LIMIT 1)
/*I assume that you already discovered ROW_NUMBER() can't use LIMIT and as
far as I know MySQL don't have TOP function. I'll not be using this.*/
FROM setoran
INNER JOIN user
ON setoran.id_user=user.id_user
GROUP BY setoran.id_user, setoran.id_sikap
/*^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
I'm pretty sure that you turned off only_full_group_by mode otherwise this query
won't successfully run with these columns in GROUP BY*/
So let's go to the first part of the query:
SELECT s.id_user, u.nama, s.id_sikap,
AVG(s.kelancaran) AS avg_k, AVG(s.tahsin) AS avg_t
FROM setoran AS s
INNER JOIN user AS u
ON s.id_user=u.id_user
GROUP BY s.id_user,
u.nama,
s.id_sikap
I retain most of your original query, added and removed a few parts. I've assigned aliases on the table so it's easier to write (s. instead of setoran.) and I also added s.id_sikap in the GROUP BY. We make this base query as a derived table and do another AVG() operation on the initial average result:
SELECT id_user,
nama,
AVG(avg_k) AS avg_kelancaran,
AVG(avg_t) AS avg_tahsin,
(AVG(avg_k)+AVG(avg_t))/2 AS akhir
FROM
(SELECT s.id_user, u.nama, s.id_sikap,
AVG(s.kelancaran) AS avg_k, AVG(s.tahsin) AS avg_t
FROM setoran AS s
INNER JOIN user AS u
ON s.id_user=u.id_user
GROUP BY s.id_user,
u.nama,
s.id_sikap) AS dt1
GROUP BY id_user,
nama;
I did not include id_sikap column here because it made little sense to show the result as A for Budi since the final average calculation is based on combined id_sikap value. However, if you still want to show them, I suggest you add GROUP_CONCAT(id_sikap) in SELECT instead of just id_sikap.
This is the result of the final query:
| id_user |
nama |
avg_kelancaran |
avg_tahsin |
akhir |
| 10000000 |
Budi |
89.75 |
87 |
88.375 |
| 10000001 |
Ina |
84 |
44 |
64 |
Notice the akhir value for Ina is different than your expected result of 50 but everything else is the same? I assume that you've made a slight mistake on the calculation since the value akhir for Budi is correct. If not, then maybe there's a logic behind that. If that's the case, we need to know ya.
Demo fiddle
SELECT id_user,
nama,
SUBSTRING_INDEX(
GROUP_CONCAT(id_sikap
ORDER BY id_skp_cnt DESC, id_sikap),
',',1) AS Most_id_sikap,
AVG(avg_k) AS avg_kelancaran,
AVG(avg_t) AS avg_tahsin,
(AVG(avg_k)+AVG(avg_t))/2 AS akhir
FROM
(SELECT s.id_user, u.nama, s.id_sikap,
COUNT(s.id_sikap) AS id_skp_cnt,
AVG(s.kelancaran) AS avg_k, AVG(s.tahsin) AS avg_t
FROM setoran AS s
INNER JOIN user AS u
ON s.id_user=u.id_user
GROUP BY s.id_user,
u.nama,
s.id_sikap) AS dt1
GROUP BY id_user,
nama
So what I did on the query was adding COUNT(s.id_sikap) AS id_skp_cnt in the derived table. The purpose for this is to count how many of the said id_sikap appeared in the row based on the grouping condition. Here's the result:
+---------+-----+---------+-----------+--------+--------+
|id_user |nama |id_sikap |id_skp_cnt |avg_k |avg_t |
+---------+-----+---------+-----------+--------+--------+
|10000000 |Budi | B | 1 |90.0000 |90.0000 |
|10000000 |Budi | A | 2 |89.5000 |84.0000 |
|10000001 |Ina | A | 1 |84.0000 |44.0000 |
+---------+-----+---------+-----------+--------+--------+
^^^
count results by existing GROUP BY
Once I did that, I use a combination of two function; the first one is GROUP_CONCAT() where I return id_sikap value with ORDER BY of the count result in descending order (DESC) from the derived table. The result of that will look like this:
| nama |
GROUP_CONCAT(id_sikap ORDER BY id_skp_cnt DESC, id_sikap) |
|
| Budi |
A, B |
The id_sikap=A for Budi gets the first position in the GROUP_CONCAT() because the count result is larger and I do order by descending |
| Ina |
A |
|
As for the second column of id_sikap appeared in the GROUP_CONCAT() order by, the reason is simple if some of the id_sikap have same count then the smaller alphabet value will take the first spot; I leave it for you to decide whether to include or not.
The last function is using SUBSTRING_INDEX() to get the first position value in the GROUP_CONCAT().
