How to query count over 2 columns

Question

I'm trying to write a sql query that shows how often two teams have played against each other.

Id | Team1 | Team2 | Date 
1  |   A   |   B   | 25/5/11
2  |   B   |   A   | 26/5/11
3  |   A   |   C   | 27/5/11
4  |   C   |   B   | 28/5/11
5  |   A   |   B   | 28/5/11

result should be:

A vs B => 3
A vs C => 1
C vs B => 1 

Counting A-B and B-A as different is an easy query. But I can't get them to be counted together.

Any suggestions?

Answer 1

I've used a sub-query to reorder the teams before grouping.

SELECT first_team, second_team, count(*)
FROM (
    SELECT 
        CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END AS first_team,
        CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END AS second_team
    FROM table
) a
GROUP BY first_team, second_team;