Re: SQL-help needed

[Michael Stassen]

Carl Schéle, IT, Posten wrote:
Hi!

I got a table, champions, looking like this:

id        class     winner_1              winner_2             year
---------------------------------------------------------------------
0          hd         carl                  mattias              1957
1          hs         daniel                                     1982
2          hd         erik                  carl                 1985
3          js         erik                                       1974
Imagine I want to see how many times each winner appears where
class=hd and which year. In this case the answer would be:
2 carl 1957,1985

1 mattias 1957

1 erik 1985

Please help! Still using old MySQL 3.23.58.
The following is close to what you want:

CREATE TEMPORARY TABLE champs (winner CHAR(10), year INT);
# change the column types to match table champions
INSERT INTO champs SELECT winner_1, year
FROM champions WHERE class='hd';
INSERT INTO champs SELECT winner_2, year
FROM champions WHERE class='hd' AND winner_2 IS NOT NULL;
# some of your winner_2 spots are empty.  If they're empty strings
# instead of NULL, change "IS NOT NULL" to != ''
SELECT * FROM champs ORDER by winner, year;
+---------+------+
| winner  | year |
+---------+------+
| carl    | 1957 |
| carl    | 1985 |
| erik    | 1985 |
| mattias | 1957 |
+---------+------+
4 rows in set (0.01 sec)
SELECT count(*), winner FROM champs GROUP BY winner;
+----------+---------+
| count(*) | winner  |
+----------+---------+
|        2 | carl    |
|        1 | erik    |
|        1 | mattias |
+----------+---------+
3 rows in set (0.01 sec)
=======

It seems to me that your table design is what makes this difficult.  If 
you changed it to something like the following, where wintype denotes 
winner_1 or winner_2, this would be easier:

 id  class  winner   wintype  year
 ---------------------------------
  1  hd     carl           1  1957
  2  hs     daniel         1  1982
  3  hd     erik           1  1985
  4  js     erik           1  1974
  5  hd     mattias        2  1957
  6  hd     carl           2  1985
You could then go straight to the select:

  SELECT * FROM champions WHERE class='hd' ORDER by winner, year;

or

  SELECT count(*), winner FROM champions
  WHERE class='hd' GROUP BY winner;
You could use a variant of the INSERT-SELECTs above to fill the new 
table, if you decide to go that way.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]