How to do a group by on a concatenated Field?

Say I have a table for students attending school like this:

St_ID |St_Name|Class_ID|Year
------+-------+--------+----
 02开发者_StackOverflow社区345|John   |     345|2011
 07871|Jane   |     234|2010

and so on. I wish to run queries on each Class which I can get by concatenating Class_ID & Year. i.e 345-2011 is one class

How do I do count(*), by having the concatenated string in the Group by query?

Two options:

1) Add the concatenated column to the group by clause

Eg.

SELECT columna || columnb || columnc
FROM table
GROUP BY columna || columnb || columnc

2) Add each column that apart's the concatenated column in the group by seperately

Eg.

SELECT columna || columnb || columnc
FROM table
GROUP BY columna, columnb, columnc

You don't need to concatenate them, just list all three columns in the GROUP BY statement.

If you do have to concatenate then just put the concatenate command in the GROUP BY.

select Class_ID||Year as class_year, count(*)
from table
group by 1

With group by you can refer to the column number.

Anal-rententives will suggest you should refer to the expression, like this: group by Class_ID||Year, but I think it's clearer as the number, because it's easier to read and especially because if you later change the expression, you don't have to remember to also change the expression in the group by.