MySQL query with GROUP BY and JOIN

Question

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):

Kind regards,

Remove b.id,c.zona from select as you don't need a group by on those columns. — Vamsi Prabhala
– Vamsi Prabhala, Commented Feb 2, 2017 at 14:12
That group by is invalid. Wont execute on newer MySQL versions (unless in compatibility mode), will return unpredictable result with older versions... — jarlh
– jarlh, Commented Feb 2, 2017 at 14:12
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
– jarlh, Commented Feb 2, 2017 at 14:13
Possible duplicate of Error related to only_full_group_by when executing a query in MySql — philipxy
– philipxy, Commented Oct 21, 2017 at 1:46

Laurent PELE · Accepted Answer · 2017-02-02 14:24:34Z

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

answered Feb 2, 2017 at 14:24

Laurent PELE

4873 silver badges9 bronze badges

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

5 Comments

Jordi Gámez Over a year ago

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.

Laurent PELE Over a year ago

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 Over a year ago

See use of a subquery to sort group_concat per degressive count stackoverflow.com/questions/7101008/…

Jordi Gámez Over a year ago

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.

Laurent PELE Over a year ago

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

Rakesh Kumar · Accepted Answer · 2017-02-02 14:42:05Z

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

edited Feb 2, 2017 at 14:42

answered Feb 2, 2017 at 14:33

Rakesh Kumar

4,4522 gold badges19 silver badges30 bronze badges

7 Comments

Jordi Gámez Over a year ago

Hi @Rakesh Kumar, it says: "Invalid use of group function". Why is it showing that error? Everything seems right. Regards.

Jordi Gámez Over a year ago

Hi @RakeshKumar, it returns an empty result. Regards.

Rakesh Kumar Over a year ago

i think this will work for you, in case not then try **Having counted_zone = MAX(counted_zone) **

Jordi Gámez Over a year ago

Also empty with this change @RakeshKumar. Regards.

Rakesh Kumar Over a year ago

can you create tables and sample data in sqlfiddle

|

Collectives™ on Stack Overflow

MySQL query with GROUP BY and JOIN

2 Answers 2

5 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related