Group By different results

Question

Group By different results

Navigation

#1 by (6 votes)
#2 by (4 votes)

3

Hello, I have a question if anyone can help me about SQL. I know the Group By clause, as far as I know how to group similar data. But I understand that it should group but in the script below it has the group by show lines and without the group by it shows only one lines. It was not just for grouping, I thought it would suit something more aesthetic !!!

With Group By

select p.descricao
from projeto p
join depto d on p.cod_depto = d.cod_depto 
join empregado e on e.cod_depto = d.cod_depto
group by p.descricao 
having count(e.mat)>1;

NoGroupBy

selectp.descricaofromprojetopjoindeptodonp.cod_depto=d.cod_deptojoinempregadoeone.cod_depto=d.cod_deptohavingcount(e.mat)>1;

sql group-by mysqli

asked by anonymous 24.08.2017 / 22:16

2 answers

6

In the second query you are using HAVING to filter. But HAVING is to perform filters after GROUP BY . So in this case he's assuming it's to do the grouping without any conditions, reducing everything to a single record.

What is recommended in the second query is the use of WHERE , which filters results before GROUP BY .

24.08.2017 / 22:22

Click on a buttom and a prompt will appear How do I get the name of an input

score 4 · Accepted Answer

The problem with your query is not GROUP BY but HAVING .

The GROUP BY is for grouping, HAVING makes a constraint based on some aggregate function.

In case of your second query you are filtering all results that have COUNT greater than 2, but since there was no grouping, therefore an implicit grouping is considered.

Translating your first query : Find the description of all projects that have at least 2 employees registered in the departments related to it.

Translating your second query : Search for a description of all projects that have at least 1 record in the same row, but consider joining all of them.

Notice how the second query does not make sense because of the constraint? Well, it's incorrect.