galvin Posted January 21, 2009 Share Posted January 21, 2009 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..... Quote Link to comment Share on other sites More sharing options...
dreamwest Posted January 21, 2009 Share Posted January 21, 2009 Count them: SELECT matchupID, COUNT Steelers FROM team2 GROUP BY matchupID Quote Link to comment Share on other sites More sharing options...
galvin Posted January 21, 2009 Author Share Posted January 21, 2009 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 Quote Link to comment Share on other sites More sharing options...
dreamwest Posted January 21, 2009 Share Posted January 21, 2009 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 Quote Link to comment Share on other sites More sharing options...
Mchl Posted January 21, 2009 Share Posted January 21, 2009 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) Quote Link to comment Share on other sites More sharing options...
fenway Posted January 21, 2009 Share Posted January 21, 2009 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. Quote Link to comment Share on other sites More sharing options...
galvin Posted January 22, 2009 Author Share Posted January 22, 2009 Excellent, that did exactly what I needed. Thanks guys!! Quote Link to comment Share on other sites More sharing options...
Zane Posted January 22, 2009 Share Posted January 22, 2009 yeah, when it comes SQL queries, fenway will take a dump in your face. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.