SQL. Finding the average between certain dates

Question

I'm trying to find the average score received by films between a certain time

SELECT SUM (score * ?) / SUM(?) AS AGGSCO
  FROM movie, casting
       INNER JOIN casting
 WHERE movie.id = casting.movieid
       AND yr BETWEEN 1930 AND 1939;

Instead of the question marks, I think I would need the number of occurrences. Following this I would need to repeat the code to find the average between 40 and 49 and then 50 to 59. Is there a loop that I can use or would I need to rewrite the code? Thanks for any advice!

ps: I'm using squirrel, not mysql so the syntax may differ

Squirrel is just a client not a server software. You are likely using MySQL as DBMS and Squirrel as an application to access the database. — Ulrich Thomas Gabor, Mar 24 '14 at 16:27

score 1 · Answer 1 · answered Mar 24 '14 at 16:25

1

select sum(score) / count(*) as AverageScote
from ...

or even better

select avg(score)
from ...

answered Mar 24 '14 at 16:25

Z .

12,657
1
31
56

very simple,can't believe I missed that. Another simple question, to get the average for the next year select AVG(score), AVG(score) FROM movie WHERE yr between 1930 AND 1939; – user3456235 Mar 24 '14 at 16:30
Do i need to use the AS keyword – user3456235 Mar 24 '14 at 16:31

score 0 · Answer 2 · edited May 23 '17 at 12:11

0

From the top of my head:

SELECT avg(score) AS AGGSCO, truncate(yr/10,0) AS DECADE
  FROM movie, casting
       INNER JOIN casting
 WHERE movie.id = casting.movieid
 GROUP BY truncate(yr/10,0)

SQL. Finding the average between certain dates

2 Answers2