Aggregate function ** Learner **

[marcoakis]

Hi guys,

I'm learning basic SQL. I have a query I can't solve.

Need to create a query named Task1D to list the last name, first name, number of matches, total number of points, for players who have played more than 10 matches and scored more than 330. A portion of the expected output is shown below.

Task2D

Last Name First Name # Match Total Points/Match
SMITH CLARENCE 16 371 23
SHAFFER BILLY 16 352 22
SCOTT ENRIQUE 16 351 22
HENDERSON GREGORY 16 346 22

Player Stats table
[img]http://img210.imageshack.us/img210/3349/sql1.gif[/img]

Player info table
[img]http://img717.imageshack.us/img717/4418/sql2.gif[/img]

Now, I know I have to use aggregate functions and use COUNT for the rows of each player, then set a rule for > 10. Then SUM and multiply the points scored by 1pt,2pt,3pt... but I'm stuck, can't seem to write the proper statement.

Thanks in advance!!

SELECT PLAYER.PLAYER_LNAME, PLAYER.PLAYER_FNAME, COUNT(MATCH_ID) AS '# Match'
FROM PLAYER_STAT, PLAYER
....

[jbisono]

This is from head so try it. Replace with your original field name.

select first_name, last_name, count(match_id) as match_total, sum(match_points) as match_points
from player
group by first_name, last_name
having count(match_id) > 10 and sum(match_points) > 330

regards

[marcoakis]

It was a bit more complicated than that, just for the record, this was the solution:

SELECT PLAYER.PLAYER_LNAME AS [Last Name], PLAYER.PLAYER_FNAME AS [First Name], COUNT(match_id) AS [# Match], sum(PLAYER_STAT.PLAYER_2PT_MADE*2+PLAYER_STAT.PLAYER_3PT_MADE*3+PLAYER_STAT.PLAYER_FREE_MADE) AS Total, ROUND(sum(PLAYER_STAT.PLAYER_2PT_MADE*2+PLAYER_STAT.PLAYER_3PT_MADE*3+PLAYER_STAT.PLAYER_FREE_MADE)/COUNT(MATCH_ID),0) AS [Points/Match]
FROM PLAYER, PLAYER_STAT
WHERE PLAYER.PLAYER_ID=PLAYER_STAT.PLAYER_ID
GROUP BY PLAYER_LNAME, PLAYER_FNAME
HAVING count(match_id)>10 And sum(PLAYER_STAT.PLAYER_2PT_MADE*2+PLAYER_STAT.PLAYER_3PT_MADE*3+PLAYER_STAT.PLAYER_FREE_MADE)>330;

[jbisono]

Cool.