having issue -- SELECT with SUM and AVG

[ntroycondo]

I am working with a database for videos. I want to know how many videos are uploaded each year and their average file size. My sql statement to retrieve number of files and their average size for each year should be??? Maybe i need SUM and AVG and grouped by year?

 

SELECT (SUM)videoid, AVG(filesize) FROM HWVideo GROUP BY date;

Table is HWVideo

Have column of videoid, filesize, date, and some others.

[kickstart]

Hi

 

Sum adds things up. Ie, you could use it to add up the total sizes of the videos. However you appear to have one row per uploaded video so you would want to COUNT them instead

 

SELECT EXTRACT(YEAR FROM uploadDate) AS uploadYear, COUNT(*), AVG(filesize)
FROM HWVideo
GROUP BY uploadYear

 

All the best

 

Keith

[ntroycondo]

Yeah, I see I need to use COUNT (*).  I'm not familiar with EXTRACT.  But I see that it is getting YEAR from DATE. The columns I have are:

videoid, date, filename, filesize, photographerid, cameraid, and locationid.

 

Is only way to get YEAR is to use EXTRACT? Is there a way to do without EXTRACT? Just curious...

 

 

[ntroycondo]

So i just figured out that you can do same without EXTRACT:

 

SELECT YEAR(date), COUNT(*), AVG(filesize) FROM HWVideo GROUP BY YEAR(date);