|
|
Hi there,
Does anyone know if i could 2 columns 1 by ascending and the other one by descending. I have 2 tables, both related by sport code:
[code]
Table: sports_played
parent_id child_id sport_code level
1 1 SW Advanced
1 1 SR Advanced
2 1 TN Intermediate
2 2 TN Beginner
2 2 HK Advanced
3 1 SR Intermediate
3 1 RG Intermediate
3 1 BS Beginner
5 1 BS Advanced
Table: sport
sport_code, sport_name, winter summer_sport
SW Swimming Yes Yes
TN Tennis Yes Yes
RG Rugby Yes No
BS Basketball No Yes
HK Hockey No Yes
SR Soccer Yes No
SL Sailing No Yes
[/code]
And what I need to have is a table that lists how many children play each summer sport at each of the levels (Beginner, Intermediate, and Advanced).
And the ordering has to be done listed in descending order of the sport_name , then in ascending order of number of children who play the sport at the level.
My SQL statements are:
[code]
SELECT sport.sport_name, Count(sports_played.level) As SumOfLevels
FROM sports_played RIGHT OUTER JOIN sport
ON sports_played.sport_code =
sport.sport_code
AND sport.summer_sport = ‘Yes’
ORDER BY sport.sport_name DESC;
SELECT sport.sport_name, Count(sports_played.level) AS SumOfLevels
FROM sports_played LEFT OUTER JOIN sport
ON sports_played.sport_code = sport.sport_code
AND sport.summer_sport = ‘Yes’
ORDER BY sports_played.level ASC;
[/code]
The reason I have 2 tables is (i thought) i can't have both sport_name Desc and sports_played ASC order. since the order will both collide with each other, and will give me an error.
Another question is can I use Count() to differentiate each 3 level, even though they are strings? Is it possible?
regards,
hulan
|
|
|
|
|
|
|
|
hulan