I try to get a certain row in MySQL but it returns a wrong value instead of the value I want to return

Question

I try to get a certain row in MySQL but it returns a wrong value instead of the value I want to return

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I would like to know how to get the row highlighted in red because I tried with this query and it returns the username: 15 and more: 2, the problem I have is that I do not know why it is returned to me by username: 21

SELECT idusuario,count(califica) as mayorpleno FROM prediccion where califica='P' GROUP BY idusuario)

when making this query I get the following image:

then to get that row highlighted in red I made this query:

    select idusuario,max(mayorpleno) from (SELECT idusuario,count(califica) as mayorpleno FROM prediccion where califica='P' GROUP BY idusuario) as cantpleno

and it returns my username: 15 fullest: 2 things that are not like that

where is the error ??

mysql

asked by fer 08.05.2017 в 00:56

source

2 answers

1

For this you must use the label HAVING

SELECT idusuario,count(califica) as mayorpleno 
FROM prediccion 
where califica='P' 
GROUP BY idusuario
HAVING mayorpleno = 2

answered by 25.07.2018 в 05:42

SII supply AEAT information. WebServiceException: class xxx do not have a property of the name NNN Extract database image

score 0 · Accepted Answer

The problem is that by mixing aggregate ( max(mayorpleno) ) and non-aggregated ( idisuario ) columns without a group by , MySQL offers no guarantee as to what value it will return for idusuario . You can find more details on this in this answer to another question.

To get the result you want, it is easier and more convenient to use a order by combined with a limit :

select idusuario, count(califica) as mayorpleno
  from prediccion
 where califica='P'
 group by idusuario
 order by mayorpleno desc
 limit 1