I highly recommend puzzling through some of these on your own first (it's
a great way to build intuition for SQL's craziness), but here's a few explanations if you're stuck.
jamie=# select a+1 from nums group by a+1;
?column?
----------
2
4
1
3
(4 rows)
jamie=# select a+1 from nums group by 1+a;
ERROR: column "nums.a" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select a+1 from nums group by 1+a;
In general, a query like `SELECT a FROM t GROUP BY a` is valid, while a query
`SELECT b FROM t GROUP BY a` is
not valid. Because you didn't group by column
b, there are multiple values of b within each group, and it's not clear how to
combine them. What you usually meant is something like
SELECT a, sum(b) FROM t GROUP BY a
where you can see the sum of all b's for each distinct value of a.
The error message explains it really nicely: if there is a GROUP BY clause,
every column in a SELECT list must either be used in the GROUP BY clause or
appear in an aggregate function. This is kind of annoying, though; what if
you want to group by a value derived from a column, like `a + 1`? (More
realistically, maybe you want to trim whitespace or something.) I think the
SQL standard would have you write
SELECT a1 FROM (SELECT a + 1 AS a1 FROM t) _ GROUP BY a1
but that's annoying. PostgreSQL (and maybe other SQL implementations; not sure!)
has some smarts about detecting when an expression is used verbatim in a SQL
query, so you can just write
SELECT a+1 FROM t GROUP BY a+1
and it will notice that the "a+1" in the SELECT list is literally the same as
the "a+1" in the GROUP BY clause. But it's not that smart, because it doesn'that
realize that "a+1" and "1+a" are semantically equivalent, and so this is an
error:
SELECT a+1 FROM t GROUP BY 1+a
----
jamie=# select b from nums group by a;
b
---
(0 rows)
jamie=# select a from nums group by b;
ERROR: column "nums.a" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select a from nums group by b;
An extension of the previous example, where PostgreSQL can infer that when
grouping by a all b's are unique. It can't infer the converse. The reason is
that a is the primary key and so guaranteed to be unique, and therefore
`GROUP BY a` is meaningless and can be elided.
(This one really confused me for a while. I only learned this recently.)
----
jamie=# select a, b from nums order by 2;
a | b
---+-----
3 | bar
0 | foo
1 | foo
2 | foo
(4 rows)
jamie=# select a, b from nums order by 1+1;
a | b
---+-----
0 | foo
1 | foo
2 | foo
3 | bar
(4 rows)
`ORDER BY <n>` means "order by the Nth column", but `ORDER BY <expr>` means
"order by the value of <expr>`. So you get the goofy behavior that `ORDER BY 2`
in the above query means order by column b, but `ORDER BY 1 + 1` means order
by the literal value "2", i.e., don't order by anything useful at all. (It just
so happens that you get primary key order in the example above, but that's not
guaranteed by any means.)
----
Most of the others have a similar feel. Let me know if you're particularly
puzzled by any others.