[SOLVED] Tough mysql query...

[galvin]

I have a table called "matchups" where there is a "team1" field and "team2" field (and a "matchupID" field which I think is insignificant for this question).  Anyway, for example, let's say I have ten rows in the table like this...

 

matchupID....team1.....team2

 

1............... Eagles.....Steelers

2................Eagles.....Steelers

3................Cardinals..Steelers

4................Eagles......Ravens

5................Cardinals..Steelers

6................Eagles.....Steelers

7................Eagles.....Steelers

8................Cardinals..Ravens

9................Cardinals..Steelers

10...............Eagles......Steelers

 

What I would want is to list out the most popular matchups, in order by the most to the least.

 

So, I would want the output to be as follows...

 

Eagles - Steelers (This one is first because there are 5 of them)

Cardinals - Steelers (This is next because there are 3)

Eagles - Ravens 1 (only one of these)

Cardinals - Ravens 1 (only one of these)

 

So, how do I structure the mysql query to do this?  I thought it would be something like this, but I think I'm way off...

 

$query = "SELECT team1, team2

COUNT(*) AS count,

FROM matchups

WHERE team1 = team1

AND team2 = team2

ORDER BY count DESC";

 

Any help would be GREATLY appreciated.  And just so you know, making a unique ID for each matchup is NOT an option.  My example was simple, but in my real table, there will be THOUSANDS of possible matchup combinations.....

[dreamwest]

Count them:

 

SELECT matchupID, COUNT Steelers FROM team2 GROUP BY 
matchupID

[galvin]

The matchupID is not unique. It is simply the primary key which automatically goes up by 1.  So the matchup ID is not an option.  I need a way to do it with just the team1 and team2 fields

[dreamwest]

Sorry only read half you post before posting..

 

Try this, make logical sense but havent tested it:

 

SELECT matchupID, COUNT Steelers FROM team2 GROUP BY
Steelers

[Mchl]

Try this, make logical sense but havent tested it:

 

Well you should. It's not even proper syntax. left alone any logic...

 

SELECT team1, team2, COUNT(*) AS count FROM matchups GROUP BY CONCAT(team1,team2)

 

 

 

[fenway]

Strictly speaking, whenever you CONCAT, I would probably recommend CONCAT_WS(), and use a separator that is unlikely to appear in your data.  Otherwise, if you have teams like:

 

team1 = me

team2 = another

 

and

 

team1 = mean

team2 = other

 

then CONCAT() with return the same string for both!  Case insensitivity can also wreak havoc.

 

I would recommend:

 

SELECT team1, team2, COUNT(*) AS count FROM matchups GROUP BY CONCAT_WS( '~',team1,team2)

 

Just to be safe.  You'll get burned eventually.

[galvin]

Excellent, that did exactly what I needed. Thanks guys!!

[Zane]

yeah, when it comes SQL queries, fenway will take a dump in your face.