Group By different results

Question

Hi, I have a question if anyone can help me on SQL. I know the Group By clause, as far as I know it groups similar data. But I understand that it should group but in the script below it has the group by shows lines and without the group by it shows only one lines. He wasn’t just for grouping, I thought he’d be good for something more aesthetic!!!

Com 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;

Sem 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
having count(e.mat)>1;

Answer 1

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

Recommended in the second query is the use of WHERE, that filters the results before the GROUP BY.

Answer 2

The problem in your query is not the GROUP BY and yes the HAVING.

The GROUP BY serves to group, the HAVING makes a restriction based on some aggregate function.

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

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

Translating your second one query: Look up the description of all projects that have at least 1 record on the same line, but consider merging them all.

You see how the second query does not make sense because of the restriction? Well, it is incorrect.