CODEWITHSUNDEEP
sqljoincount
user779214
user779214

Reputation: 13

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
2011 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?

Upvotes: 1

Views: 92

Answers (4)

Swaroop Vajrapu
Swaroop Vajrapu

Reputation: 116

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

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

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 ;

Upvotes: 1

Limey
Limey

Reputation: 2772

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.

Upvotes: 1

Rob Paller
Rob Paller

Reputation: 7786

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;

Upvotes: 1

Related Questions