help with sql query join?

Question

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?

type(type,playerid) and player(playerid,date) ... then where do we find man of the match record from? — mihsathe
– mihsathe, Commented Jun 1, 2011 at 15:29
You can group by (or "segregate" by) more than one field. Explore the "group by" syntax options. — Pete M
– Pete M, Commented Jun 1, 2011 at 15:29
sorry to get the number of awards we have to count the number of bowlers,batsmen etc — user779214
– user779214, Commented Jun 1, 2011 at 15:31
What is missing here is the Awards table itself. You have a Player Type table and a Player/Year table. The aggregation based on the information provided provides you the different types of players you had per year. (Maybe I'm reading into this too deep.) — Rob Paller
– Rob Paller, Commented Jun 1, 2011 at 16:24

Rob Paller · Accepted Answer · 2011-06-01 15:30:14Z

1

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;

answered Jun 1, 2011 at 15:30

Rob Paller

7,77631 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user779214 Over a year ago

i need to just arrange them year wise this is where i was having my problems. i cannot count the number of types

user779214 Over a year ago

This still does not give me a total no of types.... The result i get is 2011 Batsmen 1 2011 Batsmen 1 Wheras i need 2011 Batsmen 2

Rob Paller Over a year ago

My alias of Year and ordinal GROUP BY may present a challenge depending on the RDBMS used. ypercube's repsonse with the GROUP BY using the column/alias reference is more robust.

Limey · Accepted Answer · 2011-06-01 15:31:15Z

1

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.

answered Jun 1, 2011 at 15:31

Limey

2,7946 gold badges38 silver badges70 bronze badges

1 Comment

Limey Over a year ago

Then I give you an upvote for honesty. To many students come here looking for answers and not help. Its nice to see someone who actually wants to understand what is happening.

ypercubeᵀᴹ · Accepted Answer · 2011-06-01 15:43:28Z

1

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 ;

edited Jun 1, 2011 at 15:43

answered Jun 1, 2011 at 15:31

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

3 Comments

user779214 Over a year ago

This still does not give me a total no of types.... The result i get is 2011 Batsmen 1 2011 Batsmen 1 Wheras i need 2011 Batsmen 2

user779214 Over a year ago

i am able to extract the year.... just having a problem with getting the subquery to work which adds all the types together under one category.... Like total no. of batsmen who won the awad in 2011 and total no of bowlers who won it in 2011 and so on....

ypercubeᵀᴹ Over a year ago

@user779214: Have you tried this query? What result does it give you? Can you also edit your question and add at the end a small sample of your tables data?

GSerg · Accepted Answer · 2011-06-01 22:24:13Z

0

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

edited Jun 1, 2011 at 22:24

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

answered Jun 1, 2011 at 19:57

Swaroop Vajrapu

1161 silver badge7 bronze badges

Collectives™ on Stack Overflow

help with sql query join?

4 Answers 4

3 Comments

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related