SQL Query needed

lowtide

Can I do this with a single Query (MySQL)....
Scenario:
I have a table with fields id(int), date(date), sport(char), player(enum), state(char), alumni(enum).

I am collecting data from users that use our media player to listen to sporting events streamed from our site. The date is the date of the game they are listening to. The sport is the sport of that stream (sometimes more than one sport/game on a given date). The player is either Windows Media or Real Player. State is the state they are listening from. And alumni is either Alumni, Student, just a fan or fan of the opposing team.

What I would like to do is show the stats for each game. But I would like to do it with a single query instead of multiple queries within while statements (which I have already done). There must be a more streamlined way of doing this. Any ideas?

Thanks in advance

Anon

Well your database structure needs to change if you can have more than one sport on the same date, there is no way to tell the difference on which game they are listening to if there are 2 games of the same sport on the same date.
Also what stats do you want to show?

Here are is an example if you could tell the difference here is the SQL:
To get a summary for each state, player, alumni combination for a specific game do:
select state, alumni, player
from table
where QUALIFY A GAME YOU WANT TO QUERY ON
group by state, alumni, player

lowtide

There won't be more than one game of the same sport. But there could be more than one game from multiple sports on a given day. Sorry if that wasn't explained.

Basically what I'd like to have is something like this:
DATE OF GAME | SPORT | REAL | WINDOWS | STATE | FAN STATUS
2002-01-21 baskm 200 200 AL=50 Alumni=23
AR=64... Student=34...
2002-01-21 baskw 200 200 AL=50 Alumni=23
AR=64... Student=34...
2002-01-22 baskm 200 200 AL=50 Alumni=23
AR=64... Student=34...

I would probably do the state as a pulldown but I can figure that out
once I get the query that I need to build the record set.

Thanks

lowtide

See if this makes more sense...
<table border="1" cellspacing="0" cellpadding="5">
<tr>
<td>DATE OF GAME</td>
<td>SPORT</td>
<td>REAL</td>
<td>WINDOWS</td>
<td>STATE</td>
<td>FAN STATUS</td>
</tr>
<tr>
<td>2002-01-21</td>
<td>baskm</td>
<td>200</td>
<td>200</td>
<td>AL=50</td>
<td>Alumni=23</td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>AR=64...</td>
<td>Student=34...</td>
</tr>
<tr>
<td>2002-01-21</td>
<td>baskw</td>
<td>200</td>
<td>200</td>
<td>AL=50</td>
<td>Alumni=23</td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>AR=64...</td>
<td>Student=34...</td>
</tr>
<tr>
<td>2002-01-22</td>
<td>baskm</td>
<td>200</td>
<td>200</td>
<td>AL=50</td>
<td>Alumni=23</td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>AR=64...</td>
<td>Student=34...</td>
</tr>
</table>