1

Good afternoon,

I'm trying to get some information from my MySQL database and I'm having problems because I'm not able to have the information needed. I have tried a lot of different approaches and none of them have worked. I hope you can find something because I'm very close to find the solution but something is missing:

MySQL query:

SELECT b.id, b.tipo_perfil, round(avg(b.edad)), COUNT(c.zona), c.zona 
FROM analizador_datos_usuario AS a 
INNER JOIN analizador_datos_perfil AS b ON (a.id_usuario = b.id_perfil)
INNER JOIN analizador_datos_perfil_historial AS c ON (b.id = c.id_perfil)
WHERE a.id_usuario=21 
GROUP BY b.tipo_perfil, c.zona  
ORDER BY b.tipo_perfil ASC, count(c.zona) DESC

This query gives me the following information:

Table (in red it's what I need):

enter image description here

Kind regards,

Jordi Gámez
  • 3,400
  • 3
  • 22
  • 35
  • Remove `b.id,c.zona` from `select` as you don't need a `group by` on those columns. – Vamsi Prabhala Feb 02 '17 at 14:12
  • 1
    That group by is invalid. Wont execute on newer MySQL versions (unless in compatibility mode), will return unpredictable result with older versions... – jarlh Feb 02 '17 at 14:12
  • 1
    The general GROUP BY rule says: "If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function." – jarlh Feb 02 '17 at 14:13
  • 1
    See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Feb 02 '17 at 14:15
  • Possible duplicate of [Error related to only\_full\_group\_by when executing a query in MySql](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) – philipxy Oct 21 '17 at 01:46

2 Answers2

1

try that :

SELECT  b.tipo_perfil, round(avg(b.edad)), COUNT(distinct c.zona), group_concat(distinct b.id separator ' ') as id_list, group_concat(distinct c.zona separator ' ') as zona_list
FROM analizador_datos_usuario AS a 
INNER JOIN analizador_datos_perfil AS b ON (a.id_usuario = b.id_perfil)
INNER JOIN analizador_datos_perfil_historial AS c ON (b.id = c.id_perfil)
WHERE a.id_usuario=21 
GROUP BY b.tipo_perfil
ORDER BY b.tipo_perfil ASC, count(distinct c.zona) DESC
Laurent PELE
  • 487
  • 3
  • 9
  • Hi @Lauren PELE, this query it's very close but I have the latest cell with all the "zonas" I have. I think they are in the correct order but I just need the first result. Regards. – Jordi Gámez Feb 02 '17 at 14:32
  • try that, but it won't always return zone with the most rows SELECT b.tipo_perfil, round(avg(b.edad)), COUNT(distinct c.zona), group_concat(distinct b.id separator ' ') as id_list, SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(distinct c.zona separator '#'), '#', 1), '#', -1) as first_zona FROM analizador_datos_usuario AS a INNER JOIN analizador_datos_perfil AS b ON (a.id_usuario = b.id_perfil) INNER JOIN analizador_datos_perfil_historial AS c ON (b.id = c.id_perfil) WHERE a.id_usuario=21 GROUP BY b.tipo_perfil ORDER BY b.tipo_perfil ASC, count(distinct c.zona) DESC – Laurent PELE Feb 02 '17 at 16:22
  • See use of a subquery to sort group_concat per degressive count http://stackoverflow.com/questions/7101008/mysql-group-concat-with-an-order-by-count – Laurent PELE Feb 02 '17 at 16:31
  • I'm going to use your query @LaurentPELE because with your query I can have all the "zonas" in an array and I can find the most repeated value for each "tipo_perfil". Much appreciated. Regards. – Jordi Gámez Feb 03 '17 at 10:07
  • Great, I'm pleased it was helpful. I think there is a button somewhere saying you accept my response, that would be nice if you could clcik on it. Have a nice day – Laurent PELE Feb 04 '17 at 15:24
0

I think you are getting result what is displayed and you want result which is in red colour.

Try this modified query:-

SELECT b.id, b.tipo_perfil, round(avg(b.edad)), COUNT(c.zona) counted_zone, c.zona 
FROM analizador_datos_usuario AS a 
INNER JOIN analizador_datos_perfil AS b ON (a.id_usuario = b.id_perfil)
INNER JOIN analizador_datos_perfil_historial AS c ON (b.id = c.id_perfil)
WHERE a.id_usuario=21 
GROUP BY b.tipo_perfil, c.zona  
Having MAX(counted_zone)
ORDER BY b.tipo_perfil ASC, counted_zone DESC
Rakesh Kumar
  • 4,319
  • 2
  • 17
  • 30