Why does this query work only when I use group by?

This query works:

select p.Nombre as Nombre, c.Nombre as Categoria, s.Nombre as Subcategoria FROM Producto as p
inner join Subcategoria as s ON p.IDSubcategoria = s.ID
inner join Categoria as c on s.IDCategoria = c.ID
group by p.Nombre, c.Nombre, s.Nombre
order by p.Nombre

But when I remove the s.Nombre on the group by statement, I get this error:

Msg 8120, Level 16, State 1, Line 1 Column 'Subcategoria.Nombre' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can someone explain to me a little bit what the group by function does and why it allows the query to work?

In the interest of learning! T开发者_如何转开发hanks.

---

When you state group by p.Nombre, you are specifying that there should be exactly 1 row of output for each distinct p.Nombre. Hence, other fields in the select clause must be aggregated (so that if there are multiple rows with the same p.Nombre, they can be 'collapsed' into one value)

By grouping on p.Nombre, c.Nombre, s.Nombre, you are saying that there should be exactly 1 row of output for each distinct tuple. Hence, it works (because the fields displayed are involved in the grouping clause).

---

If you use GROUP BY clause you can have on SELECT fields:

• the fields that you already use in group by section
• agregates (min, max, count....) on other fields

One little example:

 MyTable
 FieldA  FieldB
    a      1
    a      2
    b      3
    b      5

Query:

select a, b from myTable GroupBy a

A   B
a   ?
b   ?

Which values you want to have in the field B?

a-> 1 or a -> 2 or a -> 3 (1+2)

If the first you need min(a) aggregate function. If you need 2 - max. If 3 - sum().

---

The group by function collapses those rows that have the same value in the columns specified in the GROUP BY clause to just one row. For any other columns in your SELECT which are not specified in the GROUP BY clause, the SQL engine needs to know what to do with those columns too by way of an aggregation function, e.g. SUM, MAX, AVG, etc. If you don't specify an aggregation function then the engine throws an exception because it doesn't know what to do.

E.g.

select p.Nombre as Nombre, c.Nombre as Categoria, SUM(s.Nombre) as Subcategoria FROM Producto as p 
inner join Subcategoria as s ON p.IDSubcategoria = s.ID 
inner join Categoria as c on s.IDCategoria = c.ID 
group by p.Nombre, c.Nombre
order by p.Nombre 

---

A group-by clause is only required if you use aggregate functions like COUNT or MAX. As a side effect it removes duplicate rows. In your case it is simpler to remove duplicates by adding DISTINCT to the select clause, and removing the group-by clause altogether.

select DISTINCT p.Nombre as Nombre, c.Nombre as Categoria, s.Nombre as Subcategoria FROM Producto as p
inner join Subcategoria as s ON p.IDSubcategoria = s.ID
inner join Categoria as c on s.IDCategoria = c.ID
order by p.Nombre