How to request an invoice from 2 columns

Question

I am trying to write a sql query that shows how often two teams 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 

the result should be:

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

Counting AB and BA differently is a simple query. But I can’t make them count together.

Any suggestions?

Answer 1

 SELECT Team1, Team2, SUM(num) FROM ( SELECT Team1, Team2, COUNT(*) num FROM table_name GROUP BY Team1, Team2 UNION ALL SELECT Team2, Team1, COUNT(*) num FROM table_name GROUP BY Team2, Team1 ) combined WHERE Team1 < Team2 GROUP BY Team1, Team2 

Edit: updated to cancel commands if necessary.

Note. This will work much faster than the CASE versions you got in the other answers because it will make full use of the indexes.

Edit2: Moved where even faster with indexes.

Answer 2

I used a subquery to reorder the commands 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; 

Answer 3

 SELECT (CASE WHEN Team1<Team2 THEN Team1 ELSE Team2) Team1, (CASE WHEN Team1>Team2 THEN Team1 ELSE Team2) Team2, COUNT(*) cnt FROM <table> GROUP BY (CASE WHEN Team1<Team2 THEN Team1 ELSE Team2) Team1, (CASE WHEN Team1>Team2 THEN Team1 ELSE Team2) Team2 

Answer 4

There are several ways to achieve your goal:

 SELECT Teams, Games = COUNT(*) FROM ( SELECT Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' + CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END FROM MY_TABLE ) AS T GROUP BY Teams 

OR if you are using SQL 2005/2008

 ;WITH T AS ( SELECT Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' + CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END FROM MY_TABLE ) SELECT Teams, Games = COUNT(*) FROM T GROUP BY Teams 

Both of the above will give you the same result

 /* Teams Games -------|------ A vs B 3 A vs C 1 B vs C 1 */ 

Here is a script you can play with:

 /* TEST DATA */ DECLARE @t AS TABLE ( ID INT, Team1 CHAR(1), Team2 CHAR(1), playdate [DATETIME] ) INSERT INTO @t (Team1 , Team2 , playdate) SELECT 'A' , 'B', '20110525' UNION ALL SELECT 'B' , 'A', '20110526' UNION ALL SELECT 'A' , 'C', '20110527' UNION ALL SELECT 'C' , 'B', '20110528' UNION ALL SELECT 'A' , 'B', '20110528' /* STYLE 1 */ ;WITH T AS ( SELECT Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' + CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END FROM @t ) SELECT Teams, Games = COUNT(*) FROM T GROUP BY Teams /* STYLE 2 */ SELECT Teams, Games = COUNT(*) FROM ( SELECT Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' + CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END FROM @t ) AS T GROUP BY Teams 

/ * OR, use this to simply switch columns * /

 /* STYLE 3 */ ;WITH T AS ( SELECT Team1 = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END , Team2 = CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END FROM @t ) SELECT Team1 , Team2, Games = COUNT(*) FROM T GROUP BY Team1 , Team2 /* STYLE 4 */ SELECT Team1 , Team2, Games = COUNT(*) FROM ( SELECT Team1 = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END , Team2 = CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END FROM @t ) AS T GROUP BY Team1 , Team2