help with sql query join?

i have two tables with type(type,playerid) and player(playerid,date) i have to find out for each year how many man of the match awards were won by which type of player ?

eg the table should be like

year type      noofawards
2011 batsmen       3
201开发者_开发知识库1 bowler        5
2010 batsmen       2

i can get the total number of awards won each year but cannot segregate them on type so what i get is

year noofawards
2011     3 


select year , count(year) as "Number of awards"
from
(
select to_char(p.date,'YYYY') as year 
from player p, type t
where p.playerid = t.playerid
)
group by year
order by year;

what should i do?

Did you try the following:

select to_char(p.date,'YYYY') as year
     , type
     , count(*)
from player p, type t
where p.playerid = t.playerid
group by 1,2;

Since this screams of homework, i will just give hints.

you don't need to use an subqueries and you need to actually select the Type column at some point in your query.

Just use:

GROUP BY year
       , type

• Most database systems have a function (YEAR() or similar name) to extract year from a date.
• Having field or table names which are reserved, like date is not a good idea.
• You should also try learning the JOIN syntax rather the implicit join with WHERE.

Something like:

SELECT YEAR(p.date) AS awardYear
     , t.type
     , COUNT(*) AS "Number of awards"
FROM player p
  JOIN type t
    ON p.playerid = t.playerid
GROUP BY awardYear
       , t.type
ORDER BY awardYear
       , t.type ;

This should work:

SELECT year,
       type,
       COUNT(*) as number
FROM (
       SELECT type,
              to_char(player.date,'YYYY') as year
       FROM player
       NATURAL JOIN type
     ) AS T
GROUP BY year,
         type