Trying to count how many records by year in SQL and by filetype-CodePudding

How would I make a query to select for each year since 1990 and show how many records were being processed per year as well as by file type? I have this query so far...

Select year(CollectDate) as yyyy, sum(amount/amount is the number of records/) /per year but idk how to do that/, count(FileType) as filecount
from Table
group by year(CollectDate), sum(amount), count(FileType)
order by yyyy

CodePudding user response：

I am not exactly sure if you want two counts for every distinct FileType and year, or the combination of both.

This would give you the count to every combination of year and FileType:

SELECT year(CollectDate) as yyyy, FileType, count(*) FROM table
GROUP BY year(CollectDate), FileType
ORDER BY yyyy, FileType

CodePudding user response：

Maybe you're looking for something like this. By moving the YEAR() function to the FROM clause using CROSS APPLY you don't have to repeat its use. Also, if the SUM(amount) is the number of records then AVG(amount) would be the yearly average records

Select v.yyyy,
       avg(t.amount) as avg_records,
       count(t.FileType) as filecount
from [Table] t
     cross apply (values (year(t.CollectDate))) v(yyyy)
group by v.yyyy
order by v.yyyy;